It may be an ugly hack, but... it works! I need the result in reverse order, but I can achieve that by making x negative. I guess this will also work if I have a WHERE clause with multiple IDs, I'll have to investigate that. Thanks
On Friday, November 2, 2012 10:50:56 AM UTC+1, Sergi Vladykin wrote: > > 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/-/-3lyYXWp5acJ. 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.
