Re: PLEASE GOD HELP US!

On Fri, 1 Oct 2004, Michael Paesold wrote:

> 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

Actually, I think he'd want an expression index on ((status=5),lastreply).
In simple tests (admittedly on 8.0b3) it looks like such an index can be
used rather than a separate sort step.