MySQL – Does Order of Columns in Covering Index Matter?

MySQL

I have the following covering indices:

INDEX (col1, col3); -- index 1
INDEX (col1, col2, col3); -- index 2

because I would like to support the following type of queries:

1)

  SELECT col3
    FROM my_table
   WHERE col1 = ... AND
         col2 = ...
ORDER BY col3

2)

  SELECT col3
    FROM my_table
   WHERE col1 = ... 
ORDER BY col3

I am unfamiliar with how a covering index works. Is index 1 redundant? Or does a covering index requires that the columns be side by side?

Best Answer

"Covering" is the wrong term to start with. I'll get back to that in a minute.

The optimal index for your queries can be summarized:

  • The first columns in the index must be all the = columns in the WHERE, in -any_ order.
  • The last columns in the index must be the ORDER BY columns in the same order, and either be all ASC or all DESC. (MySQL 8.0 has an exception here.)

Your index 1 is necessary and sufficient for query 2.
Your index 2 is necessary and sufficient for query 1.
Anything different would be sub-optimal.

Note that they would also be "covering" in that all the columns anywhere in the SELECT are found in the respective indexes.

Index 2 "covers" both queries, but it is not very good for Query 2.

  1. Devise the optimal index for each important query.
  2. Eliminate redundancy. Example: Given INDEX(a), INDEX(a,b), toss the former.
  3. Then think about adding some columns on the end to make an index "covering".

For those tips, plus more, see http://mysql.rjweb.org/doc.php/index_cookbook_mysql

This is "covering", but not efficient because col99 in first in the index, but not used for filtering or sorting:

INDEX(col99, col1, col2, col3)

It might be used, but only because it is covering. It can't be used for filtering or sorting.

This is "covering" and might be used to avoid sorting because the ORDER BY column(s) are first:

INDEX(col3, col2, col1)

Query 1 would be just as happy with this regardless of the cardinality of the individual columns:

INDEX(col2, col1, col3)

Back to the title question:

Does the order of columns in a covering index matter?

Answer:

  • For the sake of "covering": No.
  • For speeding up the query: It depends. And that is why I recommend starting with other 'rules' for building a good index.

An analogy

  • A table is an unordered set of things. Analog: a textbook.
  • An index is an ordered list, referencing those things. Analog: the index in the back of the textbook.
  • When everything you need to answer a question is sitting in the index (and you don't need to leaf back into the body of the textbook), then the index is "covering".

A B+Tree has two important properties:

  • It is quick to jump into the middle at any specific word.
  • It is fast to scan forward from that word.
Related Question