Hello Noel, thanks for, reply your sugestion works. BUT I do not see the reason why H2 does not support this? Binary tree which is used in H2 can support stepping backward and forward, am I right? Should not be this enhancement request? It is very often request in web application that user can user sort data ASC or DESC...
Or there should be updated documentation in crate index syntax. There is no word about fact that index can be crated on DESC column. Petr On Friday, June 8, 2012 1:56:45 PM UTC+2, Noel Grandin wrote: > > H2 doesn't support stepping through an index backwards, you would need > to create a reverse index to support that. > > Something like : > > create index idx_id_desc on test(id desc) > > > On 2012-06-07 16:04, Petr Holik wrote: > > Hello, > > > > I am little bit unsure when index is used for sorting and when not. I > > have two almost same SQL first uses sort using index and second not. > > The only difference is in DESC keyword. > > > > the SQL is as follows: > > > > select * from bills where closedtime< '2012-06-08 12:04:36.343 ' AND > > CLOSED = TRUE order by closedtime limit 1 > > select * from bills where closedtime< '2012-06-08 12:04:36.343 ' AND > > CLOSED = TRUE order by closedtime DESC limit 1 > > > > of course i can supply explain analyze if needed. Second query on same > > data is about 10 - 100 times slower(does not use index for sorting) > > > > Where is mistake or am I missing something? > > > > Sincerely Petr Holik > > > -- 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/-/9C_W4OIK71gJ. 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.
