Re: A slow query - Help please?

hubert depesz lubaczewski wrote:
> in case you can't, do something similar to this:
>
> select * from
> (
> select * from only table_a order by number desc limit 25
> union
> select * from only table_b order by number desc limit 25
> union
> select * from only table_c order by number desc limit 25
> ) x
> order by number desc limit 25;
>
> it should be faster. and yes, i know it's ugly.

I found a way that works, and is indeed quite a bit faster. It is even
uglier than what you proposed. The problem wasn't the "order by" in the
subquery, but the "order by" combined with the "union":

EXPLAIN ANALYZE
SELECT *
FROM (
SELECT number, otype, owner, snumber, dnumber, rnumber, dir, pos
FROM ONLY mm_posrel
ORDER BY number DESC
LIMIT 25
) a
UNION ALL
SELECT *
FROM (
SELECT number, otype, owner, snumber, dnumber, rnumber, dir, pos
FROM ONLY mm_menu_item
ORDER BY number DESC
LIMIT 25
) b
UNION ALL
SELECT *
FROM (
SELECT number, otype, owner, snumber, dnumber, rnumber, dir, pos
FROM ONLY mm_cms_operation
ORDER BY number DESC
LIMIT 25
) c
ORDER BY number DESC LIMIT 25;

Output of explain is attached, for those interested.

Now all we need to do is getting MMBase to do its queries like this :P
Thanks a bunch for setting me on the right track.
--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //