query mystery: union de-optimizes component queries
am 26.08.2011 23:57:54 von Dave DyerWhy 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org