Hello! Currently only different primary queries can use different indexes, nested derived tables and subqueries can also use own indexes.
SELECT * FROM TEST WHERE NAME = 'A' AND FIRSTNAME = 'B' can use only one index and it needs an index on (NAME, FIRSTNAME, …) or on (FIRSTNAME, NAME, …) columns for optimal performance. H2 doesn't have a bitmap index scan optimization, so if you have only separate indexes, only one index will be used. Some database systems can use two indexes on the same time in such queries, but even in them a two-column index should be significantly faster than bitmap index scan optimization on two indexes. -- 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/7c9bc87e-8899-4dc4-bb64-a094959fb1ben%40googlegroups.com.
