by Markus Winand.

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, const

Performs a B-tree traversal to find one row (like INDEX UNIQUE SCAN) and fetches additional columns from the table if needed (TABLE ACCESS BY INDEX ROWID). 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 “Using Index” to check whether the table access happens or not.

ref, range

Performs a B-tree traversal, walks through the leaf nodes to find all matching index entries (similar to INDEX RANGE SCAN) and fetches additional columns from the primary table store if needed (TABLE ACCESS BY INDEX ROWID). See “Using Index” to check whether the table access happens or not.

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”. However, if a clustered index is used (e.g., the PRIMARY index when using InnoDB) “Using Index” does not appear in the Extra column although it is technically an Index-Only Scan. 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.

Support My Work

I offer SQL training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.

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.

Previous pageNext page

You can’t learn everything in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up. Have a look at modern-⁠sql.com as well.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license