Order of columns in a multi-column index in MySQL

Question

I'm trying to understand what is better when defining multi-column indexes:

• Putting the most selective column first (higher cardinality, for speed?); or
• Putting the less selective column first (Lower cardinality, for index compression?)

Or maybe it depends if I'm optimizing for speed or space?

Answer 1

The order of the columns should match the order in which the columns are queried later or MySQL will not use them. This is the question you should really think about.

Read more here.

UPDATE:

For your question about cardinality maybe read this. Is this similar to your question? Does it answer it?

Answer 2

Always put the most selective column in the beginning, there is very rarely a reason for the other way around.

or maybe it depends if I'm optimizing for speed or space?

Let me put it this way. What is the point of using less storage, if it causes the index not to be used at all? A low cardinality index (going in column order) will normally not be used if it is not a covering index for the query, because it will be terribly expensive to go back to the data for other columns.

The point of indexes is to assist the query and having them in the right order (cardinality) should always be the first and foremost consideration.