MySQL EXPLAIN step by step - Part 12011-04-02 17:16:37 0 comments
All demonstration statements were run on Sakila sample database which can be downloaded from here. Lets start with a first, very simple query:
SELECT * FROM `sakila`.`film`;
As a result we get all rows and columns from film table. Now let see what we get when we precede previous query with a EXPLAIN word. Query:
EXPLAIN SELECT * FROM `sakila`.`film`;
Result should be similar to this:
One row and five non null columns: id, select_type, table, type, rows. Some of those columns, like id, table, rows, are self-explanatory (it doesn't mean that they always be, but right now they are), some look much more cryptic, so lets look closer to them.
id is simply a number that identifies query - we got one statement so it's marked as 1.
select_type shows the kind of SELECT we are dealing with. Basically we can distinguish between simple and complex statements. At this point this columns doesn't give much information because it's clear that query was SIMPLE. Other possible types will be discussed later.
table we can see that film table was used in this query. Quite obvious.
type according to the MySQL manual this columns show a join type. You may find this a bit strange considering lack of any joins and other tables. We should look at this table wider that a join type. Please look on the quote below from High Performance MySQL.
It's more accurate to say the access type - in other words, how MySQL has decided to find rows in the table.
So how MySQL find ALL rows? It have to do a full table scan, read each row in table. On manual we can find it information that the ALL value is the worst of all. Why? Because it means a server has a really much to do, but in query like this, when we want to find all rows in table there is no other option.
rows column shows as a value an estimated number of rows that server must read to find what we want. It's quite interesting that this number can be wrong, it's just an estimation made by MySQL. Actually film table has a 1000 rows...
Lets make a brief summary what information we get from EXPLAIN statement: we know that we made a one simple SELECT and that to get the result MySQL had to read 953 rows from film table. Pretty nice for a few additional letters before source statement.
OK, lets try something more complex. A query that gives as a result list of all films starting with letter "f".
EXPLAIN SELECT * FROM `sakila`.`film` WHERE title LIKE 'f%'
If everything went all right you should see something like on screen below.
Probably you've already notices that this time there is only a one null column. Lets look at rest of them:
id - just like a a last time, number identifying our query.
select_type - again we have a SIMPLE SELECT.
table - once again, we are selecting rows from film table.
type - at last some new values - a range access type. What does it mean? Explaining our first query we get an ALL for a access type because there wasn't any conditions. This time we were looking for titles starting with letter f - a subset of table. It makes a perfect sense, doesn't it? (actually it's more complicated, but lets assume at this point that it's clear).
possible_keys - in this column we can find a list of indexes which could be helpful during a query execution. MySQL shows that idx_title is a possible key and if we do a
SHOW INDEX FROM filmquery we'll get to know that on title column there is really a index called idx_title.
key - this column shows which of indexes was chosen to execute query as most efficient as possible. We've got only one candidate for this role and it was chosen. Certainly there are also other scenarios but at this point let stick by our example.
key_len - a maximum length of key that could possibly used in query. I wrote "possibly used", not "used", because EXPLAIN statement doesn't measure values in rows, it simply looks only at table definition.
rows - 46 rows had to be red to serve the result.
Extra - it's an additional information about query execution. Looking at out query "Using where" shouldn't be surprise in this place, but honestly I've to admit why there is "Using where" instead of "Using index".Using where mean that result was filtered just before sending it to the client. Why at the stage of reading records? Defenitly have to read more about it. If you have any clues please leave comment below.
Now it's time for a second summary. We've made one simple select statement from film table, which returned 46 rows, which used idx_title (that was only possible key and whose maximum length is 767) and range table access.
So first EXPLAINs behind us. In next part I'll try to show and tell about a few more values that could appear when you're working just on a single table.