List:General Discussion« Previous MessageNext Message »
From:Dave Dyer Date:August 26 2011 9:57pm
Subject:query mystery: union de-optimizes component queries
View as plain text  
Why would using UNION cause the subqueries to be de-optimized?

explain
(SELECT count(gamename) as gname ,variation from zertz_gamerecord 
where  (gmtdate > date_sub(current_timestamp(),interval 90 day)) and (player1='13213'
or player2='13213' ) group by variation limit 3)  

shows using index on gmtdate

explain
(SELECT count(gamename) as gname ,variation from mp_gamerecord 
  where  (gmtdate > date_sub(current_timestamp(),interval 90 day)) 
  and (player1='13213' or player2='13213' or player3='13213' or player4='13213' or
player5='13213' or player6='13213') 
  group by variation limit 3)

shows using index gmtdate

explain
(SELECT count(gamename) as gname ,variation from zertz_gamerecord 
where  (gmtdate > date_sub(current_timestamp(),interval 90 day)) and (player1='13213'
or player2='13213' ) group by variation limit 3) 
UNION 
(SELECT count(gamename) as gname ,variation from mp_gamerecord 
  where  (gmtdate > date_sub(current_timestamp(),interval 90 day)) 
  and (player1='13213' or player2='13213' or player3='13213' or player4='13213' or
player5='13213' or player6='13213') 
  group by variation limit 3) 

ie: the same two queries shows using no indexes on the first half
of the query.

Thread
query mystery: union de-optimizes component queriesDave Dyer26 Aug