From: Rick James
Date: June 16 2010 6:16pm
Subject: Re: potential query planner bug with "order by" and "limit by"?
List-Archive: http://lists.mysql.com/internals/37946
Message-Id: <4C191506.2090206@yahoo-inc.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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