Re: PLEASE GOD HELP US!

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