how much indexes are used for optimization

[Rinse Lemstra]

from the H2 website:

If a table has multiple indexes, sometimes more than one index could be used. Example: if there is a table TEST(ID, NAME, FIRSTNAME) and an index on each column, then two indexes could be used for the query SELECT * FROM TEST WHERE NAME='A' AND FIRSTNAME='B', the index on NAME or the index on FIRSTNAME. It is not possible to use both indexes at the same time.

In what conditions more then one index can be used?

regards,

Rinse

[Evgenij Ryazanov]

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.

[Christian Buchegger]

"more than one index could be used" in that context means: the optimizer may choose from more than one index but finally has to use only one of theese. 

To my knowledge the optimizer considers all available indexes an decides based on the cost.

In short all indexes are used to calculate the cost and only one is finally used to access the data.

Hope this helps.

--
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 h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/4e853fca-fab5-4955-b648-14083480ccc4n%40googlegroups.com.

[Noel Grandin]

Run the EXPLAIN command to see which indexes are used for a specific query