Interesting. So what you are saying is that because the value of x is a number then the ordering is automatically preserved because of the index on (id, x)? Sounds like a hack, but I'll try it out.
On Friday, November 2, 2012 10:33:09 AM UTC+1, Sergi Vladykin wrote: > > 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/-/BTqJ2EiqEUAJ. 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.
