MySQL EXPLAIN Operations


MySQL reference: http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

Index and Table Access

MySQL’s explain plan tends to give a false sense of safety because it says so much about indexes being used. Although technically correct, it does not mean that it is using the index efficiently. The most important information is in the TYPE column of the MySQL’s explain output—but even there, the keyword INDEX doesn’t indicate proper indexing.

eq_ref

Performs a B-tree traversal only. The database uses this operation if a primary key or unique constraint ensures that the search criteria will match no more than one entry. See also Anatomy of an SQL Index.

ref, range

Performs a B-tree traversal and walks through the leaf nodes to find all matching entries (similar to INDEX RANGE SCAN). See also Anatomy of an SQL Index.

index

Reads the entire index—all rows—in the index order (similar to INDEX FULL SCAN).

ALL

Reads the entire table—all rows and columns—as stored on the disk. Besides high IO rates, a table scan must also inspect all rows from the table so that it can also put a considerable load on the CPU. See also “Full Table Scan”.

Using Index (in the “Extra” column)

When the “Extra” column shows “Using Index”, it means that the table is not accessed because the index has all the required data. Think of “using index ONLY”. See also Clustering Data: The Second Power of Indexing.

PRIMARY (in the “key” or “possible_keys” column)

PRIMARY is the name of the automatically created index for the primary key.

There is something for everyone:
training, tuning and literature on SQL performance

Sorting and Grouping

using filesort (in the “Extra” column)

“using filesort” in the Extra column indicates an explicit sort operation—no matter where the sort takes place (main memory or on disk). “Using filesort” needs large amounts of memory to materialize the intermediate result (not pipelined). See also “Indexing Order By”.

Top-N Queries

implicit: no “using filesort” in the “Extra” column

A MySQL execution plan does not show a top-N query explicitly. If you are using the limit syntax and don’t see “using filesort” in the extra column, it is executed in a pipelined manner. See also “Querying Top-N Rows”.

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
718
views

different execution plans after failing over from primary to standby server

yesterday Markus Winand ♦♦ 741
oracle index update
1
vote
1
answer
51
views

Generate test data for a given case

2 days ago Markus Winand ♦♦ 741
testcase postgres
0
votes
1
answer
196
views

Database design suggestions for a data scraping/warehouse application?

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