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?