What happens if you have
GROUP BY c
(no ORDER BY)?
A guess -- the switch to (c,...) was thinking of the above case, but
failing to realize that it hurts your case. Write a bugs.mysql.com.
Your case (and tentative wording of the bug):
GROUP BY c
ORDER BY x -- not c
INDEX(c, ...) -- including a and x in any order
when it should use
INDEX(a, ...) -- including c and x in any order
Suggest you include EXPLAINs and toss b and d (if they are irrelevant).
For the EXPLAIN, be sure that a>1000 is less than 10% of the rows.
On 6/15/10 3:57 PM, Zardosht Kasheff wrote:
> Hello all,
> 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?
Rick James - MySQL Geek