Database - Index on multiple columns

When I run the EXPLAIN command on my MySQL query:

EXPLAIN SE开发者_如何转开发LECT colZ FROM table1, table 2 
WHERE table1.colA = table2.colA
AND table1.colB = table2.colB
AND table1.colC = X

The EXPLAIN command states that the possible_keys include:

colA, colB, colC

But the actual key used as colA

Question: Does this imply I should make an index on all three combined colums? Index (colA, colB, colC)?

Looking at your query, you will get performance with:

Index(ColA, ColB) on table2

Index(ColA, ColB, ColC, ColZ) on table 1

These indexes will allow an index-only lookup of ColZ and should be v. fast

Yes, I would recomend you create the Combined index

If you create a covering index - i.e. one that ensures the database engine can retrieve all needed information from the index alone - then that should make life easier for the optimzer. In your case, it would need to cover the columns in the WHERE clause (an index on colA, colB, colC in table1; another on colA, colB in table2).

Mantaining this index/indexes will require a certain amount of overhead, but whether the advantages outweight the extra maintenance will be specific to your setup (INSERTs vs. SELECTs etc.etc.)

The right answer depends on the selectivity of the various columns. In particular, how many different values are there for table1.col1C? If there are a total of two values (such as 'M' and 'F' in the entire table, then you are better off not even including it in the combined index. If however, there are about a third as many distinct values in col1C as there are rows in table2, then it's possible that an index on just table1.col1C will give blindingly fast results.

If the optimizer can find three rows in table1 without doing any joins, and then do a lookup join three times to get matching rows in table2, that will be very, very fast.

Barring extensive knowledge of how the optimizer works, and extensive analysis of the data population, you are probably better off to try a couple of alternatives and run benchmarks.

Here's the good news: you can always change the index design later on.