Below is a test case showing a slow query I'm observing with H2
1.2.133.
The SEKECT at the end of this script takes about 5 seconds on a 1 year
old iMac running Snow Leopard. I would expect it to be much faster, as
EXPLAIN shows that two single column indexes are being used.
I'd be grateful if you could take a look, Thomas et al.
Regards,
Steve McLeod
-- set up the test
DROP TABLE IF EXISTS test;
CREATE TABLE test (
gameid INTEGER primary key,
starttime datetime DEFAULT NOW()
);
CREATE INDEX test_starttime_idx ON test(starttime DESC);
INSERT INTO test (gameid) SELECT x FROM SYSTEM_RANGE(1, 1000000);
-- ensure there are no table scans happening. simple single column
indexes are used
EXPLAIN SELECT gameid FROM test WHERE gameid IN (SELECT TOP 10 gameid
FROM test ORDER BY starttime DESC);
-- here's the slow query
SELECT gameid FROM test WHERE gameid IN ( SELECT TOP 10 gameid FROM
test ORDER BY starttime DESC)
--
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.
