CODEWITHSUNDEEP
mysql
Kshitij
Kshitij

Reputation: 37

MySQL query case statement in where optimization

I am writing a query in mysql i am not familer with the recursive query to much. how and what i need to do to optimization of the below query as the conditions i put not looks good ,there must be a easier way to do same.

 select
   b.entity_id 
from
   entity_hierarchies a,
   entity_hierarchies b 
where
   a.entity_id = 25 
   and a.entity_type = 'user' 
   and b.entity_type = 'idea' 
   and a.Geography_Geography = 
   case
      when
         a.Geography_Geography is null 
      then
         a.Geography_Geography 
      else
         b.Geography_Geography 
   end
   and COALESCE(a.Geography_Country, '') = 
   case
      when
         a.Geography_Country is null 
      then
         COALESCE(a.Geography_Country, '') 
      else
         b.Geography_Country 
   end
   and COALESCE(a.Geography_DistrictOrCounty, '') = 
   case
      when
         a.Geography_DistrictOrCounty is null 
      then
         COALESCE(a.Geography_DistrictOrCounty, '') 
      else
         b.Geography_DistrictOrCounty 
   end
   and COALESCE(a.Geography_State, '') = 
   case
      when
         a.Geography_State is null 
      then
         COALESCE(a.Geography_State, '') 
      else
         b.Geography_State 
   end
   and COALESCE(a.Geography_City, '') = 
   case
      when
         a.Geography_City is null 
      then
         COALESCE(a.Geography_City, '') 
      else
         b.Geography_City 
   end

Upvotes: 0

Views: 57

Answers (1)

Menelaos
Menelaos

Reputation: 26005

Intro

I noticed you could rewrite some of these statements in a much simpler form.

So for example:

and a.Geography_Geography = 
   case
      when
         a.Geography_Geography is null 
      then
         a.Geography_Geography 
      else
         b.Geography_Geography 
   end

can simply become:

AND ( a.Geography_Geography is null or a.Geography_Geography =   b.Geography_Geography )

Final Solution

select
   b.entity_id 
from
   entity_hierarchies a,
   entity_hierarchies b 
where
   a.entity_id = 25 
   and a.entity_type = 'user' 
   and b.entity_type = 'idea' 
AND ( a.Geography_Geography is null OR a.Geography_Geography =   b.Geography_Geography )
AND ( a.Geography_Country is null  OR  a.Geography_Geography = b.Geography_Country )
AND ( a.Geography_DistrictOrCounty is null  OR a.Geography_DistrictOrCounty =  b.Geography_DistrictOrCounty )
AND (   a.Geography_State is null  OR   a.Geography_State =   b.Geography_State )
AND ( a.Geography_City is null  OR  a.Geography_City =   b.Geography_City );

Upvotes: 2

Related Questions