Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com May 15 '13 at 8:55

This question came from our site for professional and enthusiast programmers.

4 Answers 4

This is no single answer

The optimiser will choose an index that best suits the predicates of the query.

This depends on

  • statistics/selectivity
  • index key order and includes/covering
  • number of predicates

You may also have index intersection or key lookups where 2 or more indexes are used

share|improve this answer

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 (foo) and one on (foo, bar) the first one is redundant as every query that would benefit from it could also use the second one.

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

share|improve this answer
2  
+1 though there are edge cases where lookups of foo on an index (foo) will be much faster than lookups on (foo,bar). –  Jack Douglas May 15 '13 at 7:33
    
@JackDouglas: You are probably thinking of index-only ("covering index") retrieval, right? In that case, you are right a index-only retrieval from a single column index is bound to faster. –  a_horse_with_no_name May 15 '13 at 7:45
    
If we throw in range retrievals as well as lookups, and merge joins, it depends even more on how the optimizer works. –  Walter Mitty May 15 '13 at 9:57

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.