If I have multiple indexes that include a common attribute - will each index be searched upon a query involving this attribute? Or is only one index searched?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
migrated from stackoverflow.com May 15 '13 at 8:55
This question came from our site for professional and enthusiast programmers.
This is no single answer
The optimiser will choose an index that best suits the predicates of the query.
This depends on
You may also have index intersection or key lookups where 2 or more indexes are used
It depends very much on the DBMS being used. Some can never user more than one index per table and query other can do so.
But if there are multiple indexes sharing the same column, I doubt that even the DBMS that can use more than one index would do it, especially if the leading columns are identical.
If there is an index on (
I highly recommend the site "Use the index Luke", it has a lot of in-depth information about how indexes work and how they are used including the differences for most of the popular DBMS
Depends on all the WHERE, JOIN, GROUP BY, ORDER BY (etc.) clauses in your query, and how well the DBMS query optimizer can use each of the indexes to satisfy them. It could use one index or all or anything in between, and different DBMSes will often make different choices.
It is even possible no index will be used even though it could theoretically satisfy the given query. This can happen when you retrieve a large percentage of the all rows in the table1, in which case doing the full table scan can actually be faster than scanning the index + accessing the table heap. The DBMS will be more inclined to do the full table scan if the index has poor clustering factor, or when the table is very small.2
That's why it is important to always test on representative amounts of data. If you test on a database that is too small, or contains test data that is too different from real data, DBMS may choose different execution plans from what will end-up being used in production.
1 As a very rough guideline: more than 10%.
2 E.g. if the whole table fits into a single page, loading the page containing the index would effectively double the I/O. The I/O tends to be more important performance bottleneck than the CPU.
Most questions and answers assume some sort of "binary tree indexes", but there are other sorts of indexes, perhaps most notably a bitmap index -- which as its name suggests is a bitmap per value with one bit per row to indicate (in)equality with the value. In comparison to tables and binary indexes, bitmap indexes require very little storage and are consequently very fast to work with.
The big thing with bitmap indexes is that they CAN and SHOULD be combined for OR, AND and XOR operations on multiple values.
See http://en.wikipedia.org/wiki/Bitmap_index for a further overview.