From: Zardosht Kasheff Date: June 17 2010 7:43pm Subject: Re: potential query planner bug with "order by" and "limit by"? List-Archive: http://lists.mysql.com/internals/37947 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Filed bug 54599 on the issue Thanks -Zardosht On Wed, Jun 16, 2010 at 2:16 PM, Rick James 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 fail= ing > to realize that it hurts your case. =A0Write 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> =A01000 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> =A01000). >> >> 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: =A0 =A0http://lists.mysql.com/internals?unsub=3Dzardosht@= gmail.com > >