List:Internals« Previous MessageNext Message »
From:Rick James Date:June 16 2010 6:16pm
Subject:Re: potential query planner bug with "order by" and "limit by"?
View as plain text  
What happens if you have
(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

Your case (and tentative wording of the bug):
WHERE a>1000
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:
> (a,b,c)
> (c,b,d,a)
> 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?
> Thanks,
> Zardosht

Rick James - MySQL Geek

potential query planner bug with "order by" and "limit by"?Zardosht Kasheff16 Jun
  • Re: potential query planner bug with "order by" and "limit by"?Elaine Weisfield16 Jun
  • Re: potential query planner bug with "order by" and "limit by"?Rick James16 Jun
    • Re: potential query planner bug with "order by" and "limit by"?Zardosht Kasheff17 Jun