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]. 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.
