Getting a MySQL Execution Plan


Put explain in front of an SQL statement to retrieve the execution plan.

EXPLAIN SELECT 1;

The plan is shown in tabular form (some less important columns removed):

~+-------+------+---------------+------+~+------+------------~
~| table | type | possible_keys | key  |~| rows | Extra
~+-------+------+---------------+------+~+------+------------~
~| NULL  | NULL | NULL          | NULL |~| NULL | No tables...
~+-------+------+---------------+------+~+------+------------~

The most important information is in the TYPE column. Although the MySQL documentation refers to it as “join type”, I prefer to describe it as “access type” because it actually specifies how the data is accessed. The meaning of the type value is described in the next section.

About our book “SQL Performance Explained”
An indispensable manual for anyone, DBA, developer or system administrator
Luigi Zambetti on Amazon.co.uk (5 stars)

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/

?Recent questions at
Ask.Use-The-Index-Luke.com

0
votes
2
answers
726
views

different execution plans after failing over from primary to standby server

12 hours ago Markus Winand ♦♦ 741
oracle index update
1
vote
1
answer
57
views

Generate test data for a given case

Sep 14 at 18:11 Markus Winand ♦♦ 741
testcase postgres
0
votes
1
answer
204
views

Database design suggestions for a data scraping/warehouse application?

Aug 27 at 09:29 Markus Winand ♦♦ 741
mysql optimization database