Thanks for the explanation, I appreciate that. On Friday, November 2, 2012 10:51:58 AM UTC+1, Thomas Mueller wrote: > > Hi, > > Yes, if you have a condition "id = ?", then you actually need an index on > the columns (id, x), and order by (id, x). The problem is that the > optimizer of H2 might not understand it should use *this* index if you also > have an index on just (id) (because the index on just id seems to be the > best one initially). This is a limitation of the current optimizer > implementation (patches are welcome of course!). What you need to have is > "index sorted" in the explain plan: > > drop table if exists test; > create table test(id int, name varchar(255)); > -- create index on test(id); > @loop 1000 insert into test values(?, 'hello'); > explain select * from test > where id > 1000 order by name limit 1000; > create index on test(id, name); > analyze; > explain select * from test > where id > 1000 and name > '' order by id, name limit 1000; > > See also: http://h2database.com/html/performance.html#storage_and_indexes > > Regards, > Thomas > > > > > If it does't work for you, could you post a simple, standalone test case > please (a SQL script would be nice) > > On Fri, Nov 2, 2012 at 8:50 AM, Jan Møller <[email protected]<javascript:> > > wrote: > >> I am new to H2 and this group. So far H2 has been great for my project, >> but I have hit a show stopper. >> I have a table with a very large set of records where I need to do >> retrieve an ordered set of records with a limit. >> >> Here is my table: >> CREATE TABLE myTable(id VARCHAR(36), value VARCHAR(64), x INT) >> The table has no primary key as I need to be able to insert rows with >> duplicate IDs >> >> I have created an index to make selecting by ID fast >> CREATE INDEX index1 ON myTable(id)"); >> >> I would like to select from this table and order by x while having a >> limit as the number of records goes into the millions >> >> SELECT value, height FROM myTable WHERE id =? ORDER BY x LIMIT ? >> This takes ages, but eventually succeeds. >> >> A select like this is fast: >> SELECT value, x FROM myTable WHERE id =? LIMIT ? >> But I don't get my ordering >> >> I read here: >> http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/ >> that for MySql you should have an index on the column that you order by. >> >> So I create this index: >> CREATE INDEX index2 ON myTable(id, x) >> However this seems not to help. >> >> Is it possible to make this table and query perform well on H2? >> Note: The value of x is increasing monotonously as records are added >> (there may be rows where id1 != id2 && x1 == x2). Don't know if this can be >> used for something. >> >> Any help appreciated. >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To view this discussion on the web visit >> https://groups.google.com/d/msg/h2-database/-/K3xGgnDPBkYJ. >> To post to this group, send email to [email protected]<javascript:> >> . >> To unsubscribe from this group, send email to >> [email protected] <javascript:>. >> For more options, visit this group at >> http://groups.google.com/group/h2-database?hl=en. >> > >
-- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/NvA_GHW6-z4J. 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.
