Re: PLEASE GOD HELP US!

On Sat, 2004-10-02 at 15:42, Stephan Szabo wrote:
> On Sat, 2 Oct 2004, Scott Marlowe wrote:
> > So would a union give good performance?  Just union the first 25 or less
> > with status=5 with the rest, using a 1 and 0 in each union to order by
> > first?  Hopefully the indexes would then be used.
>
> You'd want to use union all I think and you may have to put limits on the
> unioned arms to get good behavior. I think the expression index would
> probably work without changing the query (at least for relatively low
> offsets) at the cost of having an extra index to maintain.
>

I wonder if this would offer any improvement:

select * from (
    (SELECT 2 as a,* FROM thread_listing AS t
    where t.status=5
    ORDER BY t.lastreply desc limit 25)
union
    (SELECT 1 as a,* FROM thread_listing AS t
    where t.status<>5
    order by t.lastreply desc limit 25)
) as p
order by p.a desc, p.lastreply desc limit 25;

Or is there a moving offset we have to deal with here?