Okie, here are all details:
-------------------
DDL:
CREATE TABLE TRIPPLETCOUNT (
LEFT VARCHAR(512),
RIGHT VARCHAR(512),
RELATION VARCHAR(128),
COUNT INT
);
CREATE INDEX LEFT_IN ON TRIPPLETCOUNT(LEFT);
CREATE INDEX RIGHT_IN ON TRIPPLETCOUNT(RIGHT);
CREATE INDEX RELATION_IN ON TRIPPLETCOUNT(RELATION);
CREATE INDEX COUNT_IN ON TRIPPLETCOUNT(COUNT);
--------------------------
The table has about 8 M entries.
Below are the two SQL's and to make it easier they do not find any
rows, but this does not matter to the problem at all. It is the same
scenario if I use an id that will find
many rows.
----------------------
SQL 1: takes 1+ second
explain analyze SELECT * FROM TRIPPLETCOUNT WHERE RELATION =
'REQUESTED' AND ( LEFT = 'sb_2601322' OR RIGHT= 'sb_2601322') ORDER BY
COUNT DESC
SELECT
TRIPPLETCOUNT.LEFT,
TRIPPLETCOUNT.RIGHT,
TRIPPLETCOUNT.RELATION,
TRIPPLETCOUNT.COUNT
FROM PUBLIC.TRIPPLETCOUNT
/* PUBLIC.RELATION_IN: RELATION = 'REQUESTED' */
/* scanCount: 1190147 */
WHERE (RELATION = 'REQUESTED')
AND ((LEFT = 'sb_2909322')
OR (RIGHT = 'sb_2909322'))
ORDER BY 4 DESC
/*
total: 67483
TRIPPLETCOUNT.RELATION_IN read: 17919 (26%)
TRIPPLETCOUNT.TRIPPLETCOUNT_DATA read: 49564 (73%)
*/
----------
SQL 2: takes 0.02 second
explain analyze
SELECT * FROM TRIPPLETCOUNT WHERE RELATION = 'REQUESTED'
AND LEFT = 'sb_2909322'
UNION
SELECT * FROM TRIPPLETCOUNT
WHERE RELATION = 'REQUESTED'
AND RIGHT = 'sb_2909322'
ORDER BY COUNT DESC
SELECT
TRIPPLETCOUNT.LEFT,
TRIPPLETCOUNT.RIGHT,
TRIPPLETCOUNT.RELATION,
TRIPPLETCOUNT.COUNT
FROM PUBLIC.TRIPPLETCOUNT
/* PUBLIC.RELATION_IN: RELATION = 'REQUESTED' */
/* scanCount: 1190147 */
WHERE (RELATION = 'REQUESTED')
AND ((LEFT = 'sb_2601322')
OR (RIGHT = 'sb_2601322'))
ORDER BY 4 DESC
/*
total: 67483
TRIPPLETCOUNT.RELATION_IN read: 17919 (26%)
TRIPPLETCOUNT.TRIPPLETCOUNT_DATA read: 49564 (73%)
*/
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
