Discover MyXplain, another way to learn, search, find and stroll

 

 

I’m very glad to announce you that MyXplain is released today.

This project is the result of many weeks of work for my friend Max and I and we are obviously very proud to present it.

Please take a time to browse it and find all the surprises that awaiting you.

We wanted to make an unique place, a gathering place where you can find all you want to know about the most useful MySQL commands.
We started with the explain command because we consider it as one of the most important.
show processlist and show slave status will be the next proposed commands on MyXplain.

The concept is pretty simple, we would like to offer you a new documentation to access all needed informations, slides, books and links about a particular command, in a beautiful way.

And, the most important for us was to create something alive, you can propose your links on MyXplain or add comments. In other words, you can share your expertise and your experiences with the MySQL community.

Stay curious, passionate and take your time on MyXplain!

To give a chance to MyXplain, you can share this news with your friends, your mother in law and your colleagues.
We are convinced that this type of tools can help you, in one way or the other.

One more thing, stay tuned, follow us on twitter :


Thanks

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.