Hi, You can create index on (id, x) instead of two indexes (id) + (x) and then do select just as SELECT value, height FROM myTable WHERE id =? LIMIT ? Because you selecting only one id and index already has correct order of x under each id it will return correct result. It is a still kinda dirty hack since SQL does not guarantee any ordering without ORDER BY clause, but this allows to avoid sorting at query time at all.
Sergi On Friday, November 2, 2012 11:50:08 AM UTC+4, Jan Møller 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/-/70lq-rbAtK0J. 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.
