Search code examples
sqlmysqlindexingcovering-index

Proper field orders for covering index - MySQL

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?

Solution

  • 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: