"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.
- Devise the optimal index for each important query.
- Eliminate redundancy. Example: Given
INDEX(a), INDEX(a,b), toss the former.
- 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?
- 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.
- 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.