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@xxxxxxxxxxxxxxxxx
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 //
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=7.65..7.71 rows=25 width=60) (actual time=1.352..1.678 rows=25 loops=1)
-> Sort (cost=7.65..7.84 rows=75 width=60) (actual time=1.344..1.428 rows=25 loops=1)
Sort Key: number
-> Append (cost=0.00..5.32 rows=75 width=60) (actual time=0.069..1.164 rows=25 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..1.72 rows=25 width=60) (actual time=0.063..0.877 rows=25 loops=1)
-> Subquery Scan a (cost=0.00..1.47 rows=25 width=60) (actual time=0.052..0.651 rows=25 loops=1)
-> Limit (cost=0.00..1.22 rows=25 width=60) (actual time=0.040..0.386 rows=25 loops=1)
-> Index Scan Backward using mm_posrel_pkey on mm_posrel (cost=0.00..3675.54 rows=75595 width=60) (actual time=0.032..0.187 rows=25 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..1.80 rows=25 width=60) (actual time=0.031..0.031 rows=0 loops=1)
-> Subquery Scan b (cost=0.00..1.55 rows=25 width=60) (actual time=0.023..0.023 rows=0 loops=1)
-> Limit (cost=0.00..1.30 rows=25 width=60) (actual time=0.017..0.017 rows=0 loops=1)
-> Index Scan Backward using mm_menu_item_pkey on mm_menu_item (cost=0.00..52.00 rows=1000 width=60) (actual time=0.009..0.009 rows=0 loops=1)
-> Subquery Scan "*SELECT* 3" (cost=0.00..1.80 rows=25 width=60) (actual time=0.028..0.028 rows=0 loops=1)
-> Subquery Scan c (cost=0.00..1.55 rows=25 width=60) (actual time=0.020..0.020 rows=0 loops=1)
-> Limit (cost=0.00..1.30 rows=25 width=60) (actual time=0.013..0.013 rows=0 loops=1)
-> Index Scan Backward using mm_cms_operation_pkey on mm_cms_operation (cost=0.00..52.00 rows=1000 width=60) (actual time=0.007..0.007 rows=0 loops=1)
Total runtime: 2.077 ms
(17 rows)
