Posts

Tip of The Day: Explain in MySQL

Taking a look at the MySQL documentation, you’ll see there’s a lot you can do with the MySQL explain query, and it can help you by showing you the ‘plan’ for when you execute a query. This will help you optimize troublesome queries.

When we run a query, the MySQL Query Optimizer tries to devise an optimal plan for query execution. We can see information about the query plan by prefixing the query with the MySQL EXPLAIN query. EXPLAIN helps us understand and optimize troublesome MySQL queries.

MySQL’s explain query plan tends to give a false sense of safety because it says too much about the indexes being used. The most important information is available in the TYPE column of MySQL’s explain output. However, even there the keyword INDEX does not indicate proper indexing.

It shows several bits of useful information such as…

MySQL Explain

1. id – A sequential identifier for each SELECT within the query (if you have nested subqueries)

2. select_type – The type of SELECT query. Possible values are:
SIMPLE, PRIMARY, DERIVED, SUBQUERY, DEPENDENT SUBQUERY, UNCACHEABLE SUBQUERY, UNION, DEPENDENT UNION, UNION RESULT etc.

3. table – The table referred to by the row

4. type – Indicates missing indexes or how the query that is written should be reconsidered. Possible values are:
system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery,
range, index or all

5. possible_keys – Shows the keys that may or may not be used in practice to find rows from the table. If the column is NULL, it indicates no relevant indexes could be found.

6. key – Indicates the actual index used by MySQL, this is often the optimal choice.

7. key_len – Indicates the length of the index the Query Optimizer chose to use.

8. ref – Shows the columns or constants that are compared to the index named in the key column. MySQL will either pick a constant value to be compared or a column itself based on the query execution plan.

9. rows – Lists the number of records that were traversed to produce the output.

10.Extra – Contains additional execution plan information for the query. Values such as “Using temporary”, “Using filesort”, etc. in this column may indicate a troublesome query.

Explain Extended: Helps us see the query that is executed after any transformations have been made by the Query Optimizer.

MySQL EXPLAIN