Proper field orders for covering index - MySQL

Question

Is there a standard order to create a covering index for a table in MySQL? Meaning if I have query that has a where clause, order by and the fields in the select statement, in what order would I have the fields to the index to properly create a covering index?

Answer 1

A covering index takes a list of columns in a comma separated list. This list is traversed/reviewed starting at the left side. If the left most column is not used, the index is not used. Meaning, having a column list like:

col_a, col_b, col_c

If the query does not contain a reference to col_a, it won't be used. If the order is changed to:

col_c, col_b, col_a

...then col_c needs to be referenced in the query. Continuing to use the second covering index column example, col_b or col_a don't have to be in in the query but the evaluation moves column by column, from left to right.

Column references for index use can be in the following clauses:

• SELECT
• WHERE
• GROUP BY
• HAVING
• ORDER BY

Reference:

• Multiple-Column Indexes, MySQL documentation

Answer 2

MySQL Optimization 7.5.2 Multiple-Column Indexes says:

MySQL uses multiple-column indexes in such a way that queries are fast when you specify a known quantity for the first column of the index in a WHERE clause, even if you do not specify values for the other columns.

The example on the linked page also states, that an index is not used, if you do not specify a value for the first column in the index.