The EXPLAIN command in MySQL is an essential diagnostic tool for any database developer. With it you can learn the details of how the server processes any query and therefore where to spend your time optimizing the query and/or the server indexes. It should be the first thing you check when facing a slow query.

Sample Data

For the examples below, I’m using the “world” database which you can download from the MySQL developer website. It contains city, country, and language data for many parts of the world, and is simple to understand, while having enough data for the query plans to be realistic.

The database is organized into three tables, City, Country, and CountryLanguage which look like this:

City Table (4079 rows)

CREATE TABLE City
(
  ID            INT(11)  NOT NULL AUTO_INCREMENT,
  Name          CHAR(35) NOT NULL,
  CountryCode   CHAR(3)  NOT NULL,
  District      CHAR(20) NOT NULL,
  Population    INT(11)  NOT NULL default '0',

  PRIMARY KEY (ID)
) ENGINE=MyISAM CHARSET=latin1

Country Table (239 rows)

CREATE TABLE `Country` (
  Code              CHAR(3)     NOT NULL,
  Name              CHAR(52)    NOT NULL,
  Continent         ENUM('Asia',
                         'Europe',
                         'North America',
                         'Africa',
                         'Oceania',
                         'Antarctica',
                         'South America'
                    )           NOT NULL DEFAULT 'Asia',
  Region            CHAR(26)    NOT NULL,
  SurfaceArea       FLOAT(10,2) NOT NULL DEFAULT '0.00',
  IndepYear         SMALLINT(6)     NULL,
  Population        INT(11)     NOT NULL DEFAULT '0',
  LifeExpectancy    FLOAT(3,1)      NULL,
  GNP               FLOAT(10,2)     NULL,
  GNPOld            FLOAT(10,2)     NULL,
  LocalName         CHAR(45)    NOT NULL,
  GovernmentForm    CHAR(45)    NOT NULL,
  HeadOfState       CHAR(60)        NULL,
  Capital           INT(11)         NULL,
  Code2             CHAR(2)     NOT NULL,

  PRIMARY KEY (Code)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CountryLanguage Table (984 rows)

CREATE TABLE CountryLanguage (
  CountryCode CHAR(3)       NOT NULL,
  Language    CHAR(30)      NOT NULL,
  IsOfficial  ENUM('T','F') NOT NULL DEFAULT 'F',
  Percentage  FLOAT(4,1)    NOT NULL DEFAULT '0.0',

  PRIMARY KEY  (CountryCode, Language)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Using EXPLAIN

Type “EXPLAIN” followed by any SELECT type query and the server will return a number of useful pieces of information for each table referenced in the query.

• id – server assigned integer (starting from 1) indicating the order in which the query optimizer decided to process the tables. You can force your own order by using the STRAIGHT_JOIN query hint.
• select_type – The type of query. Will be one of the following:
  • SIMPLE – Vanilla query without unions or subqueries
  • PRIMARY – Outermost SELECT query
  • UNION – Second or later SELECT query of a union
  • DEPENDENT UNION – Second or later SELECT query of a union which is dependent on an outer query
  • SUBQUERY – First SELECT query in a subquery
  • DEPENDENT SUBQUERY – First SELECT query in a subquery, dependent on an outer query
  • DERIVED – SELECT query used as a table in a FROM clause
• table – The table name or alias
• type – The access type (sometimes called the join type). This is the single most important piece of information given, as it indicates how efficiently the server was able to retrieve the requested rows of data. I cover each of the access types in detail below.
• possible_keys – Indexes which the server could possibly use to retrieve the requested rows from this table. Prior to version 5.0, MySQL was only able to use a single index per table in a query. 5.0 added the index_merge access type which allows multiple indexes to be used in certain cases.
• key – The index selected (if any) for this table. The index used is chosen based on the estimated amount of work the query optimizer thinks using each index will require. The current query optimizer attempts to minimize disk reads. A NULL value here means no index will be used, indicating a full table scan. You can use the FORCE INDEX, USE INDEX, and IGNORE INDEX query hints to guide the optimizer.
• key_len – The number of bytes of the chosen index that the server will actually use. Mostly useful for determining how many parts of a multi-part index the server used.
• rows – Estimated number of rows the optimizer thinks the server will have to examine in order to retrieve the requested rows. This number is based on table statistics and index selectivity.
• Extra – Additional information pertaining to this table.
  • Distinct – Server is able to perform an optimization where it will stop searching for more rows for the current row combination after it has found the first matching row
  • Not exists – Server is able to do a LEFT JOIN optimization where it will not examine more rows in this table for the current row combination after it finds one row that matches the JOIN criteria.
  • range checked for each record – Server found no good index to use, but checks indexes for each row. Slow, but faster than a full table scan.
  • Using filesort – Server will need to do an extra pass to sort the rows.
  • Using index – Server is able to return rows directly from the index instead of doing an additional seek to read the actual rows. Generally good news.
  • Using temporary – Server will need to create a temporary table to hold results. Often occurs with differing GROUP BY and ORDER BY columns.
  • Using where – Server is using values in the WHERE clause to limit returned rows.

Access Types

Here in order of fastest to slowest are all of the possible access types that MySQL can use when retrieving rows from a table.

system

This is a special case of the const access type where the data source is a system table.

const

This access type is used when the table has a primary key (or unique index) where each part is compared to a fixed value. Only a single row (at most) can match, and therefore the result values can be treated as constants in the rest of the query. This is extremely fast.

EXPLAIN SELECT * FROM City WHERE ID = 1

eq_ref

This access type is used when a join uses all parts of a unique, non-nullable index. A single row (at most) is read from the table for each combination of rows returned from the other joins. This is the most efficient access type for most joins.

EXPLAIN SELECT City.Name, Country.Name AS Country
        FROM City
        INNER JOIN Country ON Country.Code = City.CountryCode
        WHERE City.Name LIKE 'S%';

In this example, the server is able to locate a single row from the Country table via its primary key for each City row returned.

ref

Identical to eq_ref except that one or more rows are read from the table instead of a single row. This happens when either the join condition uses only the left most part of a multicolumn index or the index is not unique but does not contain NULL values.

EXPLAIN SELECT C.Name, L.Language
        FROM Country C
        INNER JOIN CountryLanguage L ON L.CountryCode = C.Code
        WHERE C.Name LIKE 'P%';

In this example, the CountryLanguage table has a two part primary key on (CountryCode, Language), but we are only matching on CountryCode, so multiple rows can be returned for each matching Country.

fulltext

The join is performed using a FULLTEXT index match. Note that FULLTEXT indexes and operations are not available in all MySQL database engines.

ref_or_null

Identical to ref access type but with an extra search for nullability.

ALTER TABLE Country ADD INDEX ix_Country_IndepYear (IndepYear);
EXPLAIN SELECT Name, IndepYear
        FROM Country
        WHERE IndepYear = 1975
          OR IndepYear IS NULL;
ALTER TABLE Country DROP INDEX ix_Country_IndepYear;

For this example, we have to add an index to a nullable column. Pretty much all queries using this access type have a characteristic OR [COLUMN] IS NULL clause in them.

index_merge

Multiple indexes on the same table are used to resolve multiple range conditions, then the results are merged together. Ordinarily, MySQL will use at most one index per table in a query. Using table and index statistics, it selects a single index from those available which it estimates will help the most with the given table and conditions. MySQL 5 added a new access type which can use multiple indexes under certain conditions.

ALTER TABLE Country ADD INDEX ix_Country_Region (Region);
ALTER TABLE Country ADD INDEX ix_Country_Continent (Continent);
EXPLAIN SELECT Name, Continent, Region
        FROM Country
        WHERE Continent = 'Europe'
          OR Region = 'Nordic Countries';
ALTER TABLE Country DROP INDEX ix_Country_Region;
ALTER TABLE Country DROP INDEX ix_Country_Continent;

In this example, we add an index on Continent and another on Region. Before version 5, the server would have been forced to select only one of the indexes to pull rows, resorting to scanning the results to eliminate rows not matching the other clause. Here, however, MySQL is able to use both indexes and merge (union in this case) the results.

unique_subquery

Similar to ref but for subqueries of the type IN (SELECT ... FROM ... WHERE ...) with a unique, non-nullable index. The subquery is completely replaced with an index lookup function.

EXPLAIN SELECT Name
        FROM City
        WHERE CountryCode IN (SELECT Code FROM Country
                              WHERE Continent='North America');

In this example, we use a subquery to gather all the countries in North America in order to limit the cities. While this produces the desired result, it’s more efficiently rewritten as an INNER JOIN like this:

EXPLAIN SELECT Ci.Name
        FROM City Ci
        INNER JOIN Country Co ON Co.Code = Ci.CountryCode
          AND Co.Continent='North America';

This produces the same result but uses the eq_ref access type which is generally faster.

index_subquery

Similar to unique_subquery except that the index is not unique.

ALTER TABLE Country ADD INDEX ix_Country_Code (Code);
EXPLAIN SELECT Name
        FROM City WHERE CountryCode IN (SELECT Code
                                        FROM Country IGNORE INDEX (PRIMARY)
                                        WHERE Continent = 'North America');
ALTER TABLE Country DROP INDEX ix_Country_Code;

Here we have to add a non-unique index to Country.Code and force the query optimizer to ignore the primary key in order to get the index_subquery access type.

range

This access type is used when an index exists for the given column(s) and a range of values is requested. Possible operators include =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, and IN. Note that range only occurs with LIKE when the first character is not a wildcard.

EXPLAIN SELECT Name FROM City WHERE ID BETWEEN 10 AND 100;
EXPLAIN SELECT Name FROM City WHERE ID < 10;
EXPLAIN SELECT Name FROM City WHERE ID IN (10,20,30,40);

index

A bit of a misnomer, index access type should probably be called index_scan as it results in a full scan of the index tree. This is usually better than a full table scan because the size of the index is usually smaller than the raw data. It occurs when each of the requested columns is contained in a single index, but none of the other more efficient access types apply. It always results in the Extra column of the EXPLAIN output showing “using index”.

EXPLAIN SELECT CountryCode
        FROM CountryLanguage
        WHERE Language = 'English';

Here, even though we only reference columns in the primary key for the CountryLanguage table, it lists CountryCode before Language, so eq_ref is not available, and the engine has to scan the full index to get the values we’ve requested.

ALL

A full table scan is performed for each combination of rows returned from previous tables. This can occur because no index is available for the conditions given, or no WHERE clause is given, or simply because the query optimizer estimates that a full table scan would be less costly than one of the other access methods above (usually only when the number of rows is small or index selectivity is poor). Typically, seeing the ALL access type indicates poor performance, and you can improve performance by adding appropriate indexes or rewriting the query.

EXPLAIN SELECT Code
        FROM Country
        WHERE Continent = 'Asia';

Here we see the ALL access type because there is no index available on the Country.Continent column, and the server is forced to scan each data record looking for matches. If we add an index on the Continent column, we can bump the access type all the way up to ref.

ALTER TABLE Country ADD INDEX ix_Country_Continent (Continent);
EXPLAIN SELECT Code
        FROM Country
        WHERE Continent = 'Asia';
ALTER TABLE Country DROP INDEX ix_Country_Continent;