List:Internals« Previous MessageNext Message »
From:Elaine Weisfield Date:June 15 2010 11:33pm
Subject:Re: potential query planner bug with "order by" and "limit by"?
View as plain text  
On Jun 15, 2010, at 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
>
> -- 
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
>

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