List:Internals« Previous MessageNext Message »
From:Zardosht Kasheff Date:June 15 2010 10:57pm
Subject:potential query planner bug with "order by" and "limit by"?
View as plain text  
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
Thread
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