6

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 ?

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

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. 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 '14 at 7:48
  • 1
    so the assertion in the quote isn't really valid isn't it ? – zinking Jan 20 '14 at 8:08
  • 2
    @zinking I think you misread it... – dezso Jan 20 '14 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 '14 at 11:56
7

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.

  • 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 '14 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 '14 at 11:59
  • @JürgenStrobel Have you read the linked article? use-the-index-luke.com/sql/where-clause/searching-for-ranges/… Sure there are exceptions, but for the majority it is clearly better. – Markus Winand Jan 20 '14 at 12:00
  • Define the majority. For any non trivial schema with lots of tables and different queries, a big amount of data, and precious memory at the hardware limit, NO. For toy applications which fit into memory in total, yes. – Jürgen Strobel Jan 20 '14 at 12:04
  • 1
    @JürgenStrobel If you think so, prove it. – Markus Winand Jan 20 '14 at 12:07

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