List:Internals« Previous MessageNext Message »
From:Zardosht Kasheff Date:June 17 2010 7:43pm
Subject:Re: potential query planner bug with "order by" and "limit by"?
View as plain text  
Filed bug 54599 on the issue

Thanks
-Zardosht

On Wed, Jun 16, 2010 at 2:16 PM, Rick James <rjames@stripped> wrote:
> What happens if you have
> GROUP BY c
> LIMIT 10
> (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):
> WHERE a>1000
> GROUP BY c
> ORDER BY x -- not c
> LIMIT 10
> uses
> 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
>
>
> --
> 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