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.