On 05/06/2005, "Doug V" wrote:
> In your followup message [from [EMAIL PROTECTED], 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
