Home > Admin > Covering or not covering, that is the question

Covering or not covering, that is the question

I’m currently working on a new project about the explain command output.
For my research I’ve read a lot of documentations, presentations and blog posts and I would like to focus on the join type in the explain output.
And in particular when type=index in this explain output.

Take a look at what says the official documentation about type=index :

Wait a second, only the index tree is scanned? really?
This description seems to be the definition of a covering index.
But a covering index is only characterized by a “Using index” in the extra column of the explain output.

Consider the t1 table, just two columns and a primary key on the id column :
(The table contain 5 lines)

Now, let see the explain output of this query :

sql > explain SELECT text1 FROM t1 ORDER BY id;

The join type is equal to index (type=index), the primary key is used (key=PRIMARY) but there isn’t any indication in the extra column.
The primary key is only used to sort the result-set of the query, five rows are return here (rows=5), this is the entire content of the table.

So, in this case, the index is used to sort the rows but the table is acceded to retrieve the values of the text1 column.
It’s not possible to say that only the index tree is scanned because the text1 column is not a part of the primary key, MySQL needs to access to the table.

Consider this other query :

sql > explain SELECT id FROM t1;

In this case, the type column indicates “index” and the extra column indicates “Using index”.
That means MySQL uses a covering index to retrieve data, the entire content of the index is read, only the index tree is read. MySQL doesn’t need to read the table because all the needed data are stored in the index.

The official documentation appears vague for this particular join type. When type=index, the table can be accessed yet for retrieve data.
Only the specification of “Using index” in the extra column means that only the index is accessed.

The real definition for this particular join type could be that the table is scanned in the index order (This is the definition given in the book High Performance MySQL)

Keep in mind that a full index scan is just a bit better than a full table scan but remains a bad way to access your data.

Congratulations, you've read this post till the end !
If you enjoyed this article, please leave a comment

You can also subscribe to RSS feed or by email to automatically receive real-time notification of new posts

Thanks for your visit on MySQL+. Cédric

Categories: Admin
  1. November 18th, 2012 at 12:23 | #1

    With InnoDB the index is not a separate file as the docs suggest.

    You could use performance_schema to see I/O and index usage.

    Don’t forget to fill a bug if the docs are wrong or incomplete.

  2. November 19th, 2012 at 15:02 | #3

    Oh, now I understand your confusion. It’s not that *only* the index tree is scanned, it’s that it’s scanned in the context of the query execution plan. It may very well go to the data tree for more information, but for the first pass it uses only the index tree.

    For example, in the first query, the type is index because you’re doing a full index scan on id (because there’s no WHERE clause to filter anything out) but you go to the data tree for the text1 data.

    So imagine the process like this:

    start at the beginning of the index tree, ordered by id. For the first id, follow the pointer to the data row and get the value of text1. Then go back to the index tree, and for the 2nd id, follow the pointer to the data row and get the value of text1.

    It only goes to the index for the scanning it needs to do for the EXPLAIN plan.

    Think of other “type” values, and it works the same – with a query like

    SELECT text1 FROM t1 WHERE id BETWEEN 100 AND 110;

    The “type” here would be “range”, because it’s scanning a *part* of the index tree – the part where id between 100 and 110 – and then goes to the data structure to get the value of text1. There’s no question that “range” is not a covering index.

    • November 19th, 2012 at 15:15 | #4

      Sheeri, thx for your comment.
      I understand these concepts and I haven’t any problem to consider that the index is the entry point.
      But I would like to indicate that the official documentation is ambiguous because it suggests that only the index is used.

  3. November 19th, 2012 at 15:34 | #5

    @Cédric PEINTRE
    I don’t agree, because it’s similarly ambiguous about all the other types other than ALL too – range, ref, eq_ref, etc.

    http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain-join-types – says

    “The type column of EXPLAIN output describes how tables are joined.”

    and then explains of type=index: “This join type is the same as ALL, except that only the index tree is scanned.” So it’s only scanned for the *join*, not only scanned for *every piece of information*.

    The documentation isn’t perfect (it shouldn’t call it a join type, it’s really a data access strategy, it’s not always for a join), but I think it’s clear it’s only talking about “type” in the context of “what it needs to filter the data” not “what it needs to satisfy the entire query.”

    • November 19th, 2012 at 16:52 | #6

      It all depends how you look at it but I respect your arguments.
      I believe that “using an index to select the rows” is more accurate than “only the index tree is scanned”.
      But again, this is only my opinion.

  4. November 19th, 2012 at 17:28 | #7

    *nod* I am a native English speaker and was taught database theory in college, so it might not be fair for me to say “it makes sense to me!” I think your alternate wording is good, and you should make a MySQL bug for that, it’s an easy and accurate update.

    • November 19th, 2012 at 18:37 | #8

      Yes, and I’m just a poor little French ;-)
      it was really interesting to compare my point of view with yours
      Thank you very much for your comments and for the time spent to answer me

  5. November 20th, 2012 at 03:13 | #9

    Please do file bugs for things like this. We do like to get feedback on how to improve the documentation.

    Views are my own, for an official opinion, seek a PR person.

    James Day, MySQL Senior Principal Support Engineer, Oracle

  1. November 23rd, 2012 at 15:41 | #1
  2. November 23rd, 2012 at 17:55 | #2