It is not because x is a number, but because index is a data structure sorted by given fields.
Sergi On Friday, November 2, 2012 1:41:29 PM UTC+4, Jan Møller wrote: > > 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/-/jj5M2D9JYnUJ. 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.
