2

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?

  • You can learn in-depth on index in Rick's site – James Nov 10 at 8:05
  • @James Thanks ... – Elaina Nov 10 at 9:06
  • @James I just read it. It didn't help. The part on covering index is what I had read from MySQL's official docs. – Elaina Nov 10 at 9:15
  • How many different values for col2 do exist? if the answer is 'not many', than you can use index1, if there are many different values for col2, index 2 would be better. – Luuk Nov 10 at 9:50
  • 1
    I am unfamiliar with how a covering index works. Covering index is an index which includes all fields. Regardless of the order of the fields in it - i.e. it may not match the query at all. But it may be used as a compact table copy (after removing all fields not listed in a query), i.e. the query may be executed without access to the table at all - all data for all operations may be extracted from the index. Do not forget that any index in InnoDB includes PK expression hiddenly, so index may be covering even it does not include all fields listed in the query formally. – Akina Nov 11 at 5:09
3

"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.
-2

I created a dbfiddle

All columns are evenly distributed (all columns have equal number of different values).

Dropping index1 does not alter any (tested) execution plan.

dus to comments from @Akina about not using 'covering indexes', I added some queries to this dbfiddle

Still index1 does not alter any (tested) execution plan.

  • Study what is "covering index". Your fiddle is not relative to. – Akina Nov 10 at 13:16
  • @Akina: please clarify you answer, this is a bit blunt, especially the part 'Your fiddle is not relative to'. – Luuk Nov 10 at 14:13
  • All indices in your fiddle are NOT covering ones for the queries in the fiddle. You select ALL fields (using asterisk symbol), but none index includes col4 field present in output list. So your fiddle tells about nothing. – Akina Nov 11 at 4:59
  • @Akina so, you are saying the fiddle is incorrect just because it does not use 'covering index'?. Ok, it's true it does not use a covering index, … i'm sorry I overlooked that part. – Luuk Nov 11 at 20:27
  • you are saying the fiddle is incorrect just because it does not use 'covering index'? 100% true – Akina Nov 12 at 4:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.