List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:May 1 2001 1:40pm
Subject:Re: ORDER BY DESC optimization
View as plain text  
First, you don't have an index on 'b', and second, you don't have 'b' in 
the where clause, so it would not use it if it had one.

It needs to sort the result set before it can apply the limit.

ryc wrote:

> I have a fairly large table (greater than 4mil rows) that I would to preform
> a query like:
> 
> SELECT col1,col2 FROM table WHERE a=1, c=2 ORDER BY b DESC LIMIT 50;
> 
> I have an index on the table INDEX1( a,b,c );
> 
> When running the query as is, it takes around 4seconds. If I omit the "DESC"
> part the query runs in a fraction of a second.
> 
> I would like the query to run faster when I use DESC. I looked at
> myisamchk -R to sort by the 'b' index but I want to be sure it will speed up
> my query since it may take a while to sort all 4million rows.
> 
> Does anyone have guidance on how to accomplish this? is myisamchk -R what I
> want?
> 
> Thanks.
> 
> ryan
> 
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread72737@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Gerald L. Clark
gerald_clark@stripped

Thread
ORDER BY DESC optimizationryc30 Apr
  • Re: ORDER BY DESC optimizationGerald Clark1 May
  • Re: ORDER BY DESC optimizationThalis A. Kalfigopoulos1 May
    • Re: ORDER BY DESC optimizationThalis A. Kalfigopoulos1 May
    • Re: ORDER BY DESC optimizationryc1 May
      • Re: ORDER BY DESC optimizationThalis A. Kalfigopoulos1 May