I have a MyISAM table foo with the following schema:
(a int, b int, c int, d int, ....)
and with keys:
The query is :
select c, count(*) num_cnt, from foo where a > 1000 and b is not NULL,
group by c, order by num_cnt;
Note that both keys are covering indexes.
When I run the query, MySQL properly selects the key (a,b,c). However,
when I add a "limit 10" to the end, the key (c,b,d,a) is used. Using
(c,b,d,a) is not good because the entire index will need to be read to
find the 10 values of 'c' that show up the most, whereas if (a,b,c) is
used, only a range is done (where a > 1000).
When I run this with MyISAM, the proper query plan returns in 1
second, whereas the bad one returns in 6 seconds.
Does anyone have insight into what may be going on?