List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:May 17 1999 2:25pm
Subject:Re: why is "order by" DESC so slow?
View as plain text  
Robert A. Bruce wrote:
> 
> I have a table of about 900,000 customer addresses, and an interactive
> application that can be used to search the addresses, and scroll 
> 
> mysql> select custnum,name from addresses where name < 'Smith;Joe' order by
>        name DESC limit 10;
> +---------+---------------+
> | custnum | name          |
> +---------+---------------+
> |  625488 | Smith;Joan    |
> |  464305 | Smith;Jo Anne |
> |   75994 | Smith;Jimmy   |
> |   58117 | Smith;Jim     |
> |  442798 | Smith;Jim     |
> |  593324 | Smith;Jim     |
> |  833712 | Smith;Jim     |
> |  474946 | Smith;Jesse   |
> |  883455 | Smith;Jerry K |
> |  122635 | Smith;Jerry   |
> +---------+---------------+
> 10 rows in set (54 min 18.11 sec)
> 
> I don't see why this should be true, since there is an index for the "name"
> column.  Apparently mysqld is resorting the entire table in reverse, and
> using an extremely slow algorithm to do so.
> 
> Is there an efficient way to get the rows that directly precede a value?
> 

Whenever you run across a slow query use EXPLAIN to see how it's being
processed. Have you tried the first query with and ORDER BY?

What version are you running? What are you variable settings?

  jim...
Thread
why is "order by" DESC so slow?Robert A. Bruce17 May
  • Re: why is "order by" DESC so slow?Jim Faucette17 May
  • why is "order by" DESC so slow?Michael Widenius24 May
  • Sync'ing databasesEvan Thomas1 Jun
    • Re: Sync'ing databasesJorge del Conde1 Jun
Re: Sync'ing databasesSheamus Nulty1 Jun