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.

Index Merge: Combining Multiple Indexes

It is one of the most common question about indexing: is it better to have one index for each column or a single index for all columns of a where clause? The answer is very simple in most cases: one index with multiple columns is better—that is, a concatenated or compound index. “Concatenated Indexes” explains them in detail.

I can totally understand this assertion under this scenario:

TABLE( A,B,C ) 
Indexing 1: Index A, Index B, Index C
Indexing 2: Index (A,B,C)
Query: JOIN ON X.B=B AND X.C=C

because under this scenario, Indexing 2 cannot used at all ( I know about the exceptions )

but under this scenario:

TABLE( A,B,C ) 
Indexing 1: Index A, Index B, Index C
Indexing 2: Index (A,B,C)
Query: JOIN ON X.A=A X.B=B AND X.C=C

I think Indexing 2 is better, it only needs to traverse 1 index tree instead of 3 right ?

share|improve this question

closed as too broad by marc_s, Mark Storey-Smith, dezso, Max Vernon, RolandoMySQLDBA Jan 20 at 20:38

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

5  
Highly depends on what your data and your queries look like - there's no simple, generally valid answer to this.... –  marc_s Jan 20 at 7:48
1  
so the assertion in the quote isn't really valid isn't it ? –  zinking Jan 20 at 8:08
2  
@zinking I think you misread it... –  dezso Jan 20 at 10:49
    
2 Problems: 1. Traversing 3 small indices for A,B,C doesn't take much more resources than the compound index, but those indices will be much more versatile. 2. Only thinking about maximum performance for this single query is most likely wrong, memory pressure and I/O load from many special indices will limit your total system performance. –  Jürgen Strobel Jan 20 at 11:56
add comment

1 Answer 1

up vote 7 down vote accepted

Original author here.

The quote is meant as following:

In your first query, just filtering on B and C, what would be better:

Indexing 1: Index B, Index C
Indexing 2: Index (B,C)

Clearly, Indexing 2 is better.

The context was meant to be: if I index all columns, is it better to have one index with all of them, or one separate index for every column. In that case, it is almost always better to have one with all columns.

It's a design advice, not for assessment.

share|improve this answer
    
thank you very much for clarify this. –  zinking Jan 20 at 8:44
3  
+1, just for the great site you have. –  ypercube Jan 20 at 9:09
3  
And don't forget to read the page quoted in the question! It includes specific information about the contents of the two columns as well. Just because a query restricts on 2 columns doesn't mean that an index on both columns is always better! If the first column is already unique or "mostly unique" then it may well be more efficient to just index the first column resulting in a much smaller index and to visit the actual table to check the values for the second column. –  Colin 't Hart Jan 20 at 10:32
    
Not clear. Indexing strategy 2, if done everywhere, will use much more system resources which has a very likely potential to be worse than 1, but there is no general answer to this. –  Jürgen Strobel Jan 20 at 11:59
1  
@JürgenStrobel If you think so, prove it. –  Markus Winand Jan 20 at 12:07
show 4 more comments

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