I did some more investigation. This seems to be a regression. In H2 1.2.128, the SELECT query returns in 0 ms (or thereabouts).
On Apr 15, 11:13 pm, Steve McLeod <[email protected]> wrote: > 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.
