Hi,
I have tried to simply the problem and it exists without any JOINs.
have you given the query ?
SELECT id FROM stuff ORDER BY stuffed_date DESC LIMIT 18, 10 -> .43 sec
SELECT id FROM stuff ORDER BY stuffed_date DESC LIMIT 0, 10 -> .0007 sec
have you described your tables ?
stuffed
When I do a SELECT using STRAIGHT JOIN against multiple tables where the
main table has about 200k rows, it is very fast retrieving the latest rows,
ie "LIMIT 0, 10", but extremely slow retrieving older rows, for example,
"LIMIT 18 , 10". Doing an EXPLAIN shows that no filesort or temporary
Using INNER JOINs with the STRAIGHT_JOIN option so that the main table
displayed first in the EXPLAIN eliminated the temporary table being created
and the query speed went from 2.3 seconds to 0.2 seconds. Note that this
only works for queries where the offset in the LIMIT is small. As the offset
Thank you for your detailed response.
You might get better performance just from using the explicit INNER JOINS
but I make no assumptions.
I tried INNER JOINS and did not see any difference in speed.
You may also get better performance if you had
composite indexes (not just several individual field
A query which is constantly being run takes about 3 seconds when not cached,
and I was wondering if there were any way to speed this up. There are
several tables being joined and sorted by latest date with a LIMIT of 10.
All fields being joined by are indexed. So I'm not sure what else I can do.
Will "mysql" run on Microsoft's windows ME operating system ?
Thank You,
Doug V.
-
Before posting, please check:
http://www.mysql.com/manual.php (the
