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

WHERE person_id = 123 AND country_code = 'AT'

Use

INDEX(person_id, country_code)  -- in EITHER order!

There is no difference in speed or space for the order of the index columns in this case.

Yeah, MyISAM had "index compression", but that is not used anymore.

Cardinality only matters for comparing separate indexes, not for ordering columns in a composite index. That is,

INDEX(person_id)  -- is better than
INDEX(country_code)

But neither is as good as the composite index.

For

WHERE person_name LIKE 'James%' AND country_code = 'UK'

the best index is

INDEX(country_code, person_name)   -- in THIS order!

The order in the WHERE has no impact on optimization.

More tips and discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

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.

Answer 3

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?