List:General Discussion« Previous MessageNext Message »
From:Felix Geerinckx Date:June 6 2005 9:00am
Subject:Re: Slow LIMIT Query
View as plain text  
On 05/06/2005, "Doug V" wrote:

> In your followup message [from mfatene@stripped], you mention reverse 
> sorting the query. I imagine on the application side I would need to 
> reverse sort again to get the correct order. Are there any other ways 
> to speed up such a query? 

I find similar behaviour with one of my standard testtables:

CREATE TABLE dtfoo (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        dt DATETIME,
        d VARCHAR(100),
        KEY (dt)
) ENGINE = MyISAM;

which is filled with 250000 records with random dt columns 
BETWEEN '2000-01-01 00:00:00' AND '2005-12-31 23:59:59', and in which
the d column just contains a character copy of dt (to have *some* other
data ;-).

Essentially: LIMIT clauses from the start of the SELECT are extremely
fast, while LIMIT clauses from the end of the SELECT are extremely slow
(even more so when the result set includes not only the id but also the
the dt column and/or the d column.) - we're talking factors > 1000
between fast and slow when both dt and d are included in the result set.

I guess this is because the index on dt can be used to *locate* a
record, (as in 'WHERE dt > @some_datetime'), but *not* to count how
many records come before a certain @some_datetime, wihch is needed for
a LIMIT clause.

By the way, if you don't want the reverse ordering from

SELECT id FROM dtfoo ORDER BY dt DESC LIMIT 0, 10;

you can use a subquery (if your on MySQL 4.1.x):

SELECT dtfoo2.id FROM 
(SELECT id, dt FROM dtfoo ORDER BY dt DESC LIMIT 0, 10) AS dtfoo2 
ORDER BY dtfoo2.dt ASC;

which is still very fast.

Personally, I never feel comfortable with LIMIT n,m clauses where n is
high (perhaps maybe for implementing pagination on web pages).

I always prefer to set my 'start' in the WHERE clause, so the index can
do its work, e.g:

SELECT id, dt FROM dtfoo WHERE dt > @some_datetime 
ORDER BY dt ASC LIMIT 0, 10;

-- 
felix
Thread
Slow LIMIT QueryDoug V5 Jun
  • Re: Slow LIMIT Querymfatene5 Jun
    • Re: Slow LIMIT Querymfatene5 Jun
    • Re: Slow LIMIT QueryDoug V5 Jun
      • Re: Slow LIMIT Querymfatene6 Jun
    • Re: Slow LIMIT QueryFelix Geerinckx6 Jun