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 ?