Shane | SkinnyCorp wrote:
Okay, just so no one posts about this again...
the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a status of '5' to the top of the list... it is NOT meant to only grab
threads where the status = 5. Oh and believe me, when I take this out of
the query, it CERTAINLY doesn't add any more than possible 1/4 of a
millesecond to the speed of the SELECT statement.
:/
Perhaps I missed it, but you did not yet send the output of the explain of this query, did you?
Could you please do:
EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.status=5 DESC, t.lastreply DESC LIMIT 25 OFFSET 0;
EXPLAIN SELECT * FROM thread_listing AS t WHERE t.status=5 ORDER BY t.lastreply DESC LIMIT 25 OFFSET 0;
EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.lastreply DESC LIMIT 25 OFFSET 0;
(and post the results here)
The first one will certainly do a sequential scan, the last one will use an index if available. For the second you will need a partial index on lastreply with a where clause WHERE status=5, I believe. So a solution would perhaps be to use two queries, one with WHERE t.status=5, another without. Make both use an index. Then combine them in your application. Of course this can only work if you do not need an offset other then 0.
Also, did you check that your system does not swap (using vmstat)?
Best Regards,
Michael Paesold
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
