Hi, > specifying the > reverse order in the query (asc or desc) causes H2 to not use the > index for the sort, even though logically it could.
Yes, this is a limitation of H2. Currently b-tree indexes can only scan forward. However you could create a descending index: drop table address; create table address(street varchar, state varchar, zip int); create index address_zip_desc on address(zip desc) ; explain select * from address where zip>0 order by zip desc; --> FROM PUBLIC.ADDRESS /* PUBLIC.ADDRESS_ZIP_DESC: ZIP > 0 */ --> ORDER BY 3 DESC /* index sorted */ Unfortunately, if you create two indexes on the same column (one ascending and one descending), then the wrong index is used in this example (address_zip instead of address_zip_desc). I will add a feature request for this "Allow to scan index backwards starting with a value (to better support ORDER BY DESC)" but I can't tell you when this will be implemented. Regards, Thomas --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. 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 -~----------~----~----~----~------~----~------~--~---
