Hello. H2 currently can use index-sorted optimization only when there is a compatible index with the same order. But you can create indexes with different order on the same columns.
CREATE TABLE TEST(A INT, B INT); CREATE INDEX IDX1 ON TEST(A, B); CREATE INDEX IDX2 ON TEST(A, B DESC); EXPLAIN SELECT * FROM TEST ORDER BY A; > SELECT > "PUBLIC"."TEST"."A", > "PUBLIC"."TEST"."B" > FROM "PUBLIC"."TEST" > /* PUBLIC.IDX1 */ > ORDER BY 1 > /* index sorted */ EXPLAIN SELECT * FROM TEST WHERE A = 1 ORDER BY A, B; > SELECT > "PUBLIC"."TEST"."A", > "PUBLIC"."TEST"."B" > FROM "PUBLIC"."TEST" > /* PUBLIC.IDX1: A = 1 */ > WHERE "A" = 1 > ORDER BY 1, 2 > /* index sorted */ EXPLAIN SELECT * FROM TEST WHERE A = 1 ORDER BY A, B DESC; > SELECT > "PUBLIC"."TEST"."A", > "PUBLIC"."TEST"."B" > FROM "PUBLIC"."TEST" > /* PUBLIC.IDX2: A = 1 */ > WHERE "A" = 1 > ORDER BY 1, 2 DESC /* index sorted */ -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/749022e7-51cf-486b-96d3-626f9b9e6697o%40googlegroups.com.
