Optimizing MySQL performance by using EXPLAIN — Query Execution Plan

EXPLAIN — Query Execution Plan

EXPLAIN — Output

+---------------+------------------------------------------------+
| column | Meaning |
+---------------+------------------------------------------------+
| id | The SELECT identifier |
| select_type | The SELECT type |
| table | The table for the output row |
| partitions | The matching partitions |
| type | The join type |
| possible_keys | The possible indexes to choose |
| key | The index is actually chosen |
| key_len | The length of the chosen key |
| ref | The columns compared to the index |
| rows | Estimate of rows to be examined |
| filtered | Percentage of rows filtered by table condition |
| Extra | Additional information |
+---------------+------------------------------------------------+
+----------------------+-------------------------------------------+
| select_type | Meaning |
+----------------------+-------------------------------------------+
SIMPLE | Simple SELECT
PRIMARY | Outermost SELECT
UNION | Second or later SELECT statement in a
UNION
DEPENDENT UNION | Second or later SELECT statement in a
UNION, dependent on outer query
UNION RESULT | Result of a UNION
SUBQUERY | First SELECT in subquery
DEPENDENT SUBQUERY | First SELECT in subquery, dependent on
outer query
DERIVED | Derived table
DEPENDENT DERIVED | Derived table dependent on another table
MATERIALIZED | Materialized subquery
UNCACHEABLE SUBQUERY | A subquery for which the result cannot be
cached and must be re-evaluated for each
row of the outer query
UNCACHEABLE UNION | The second or later select in a UNION that
belongs to an uncacheable subquery
+----------------------+-------------------------------------------+
+-----------------+------------------------------------------------+
| type | Meaning | +-----------------+------------------------------------------------+
system | The table has only one row
const | The table has at most one matching row
eq_ref | One row is read from this table for each
combination of rows from the previous tables.
ref | All rows with matching index values are read
from this table for each combination of rows
from the previous tables
fulltext | The join is performed using a FULLTEXT index.
ref_or_null | This join type is like ref, but with the
addition that MySQL does an extra search for
rows that contain NULL values.
index_merge | This join type indicates that the Index Merge
optimization is used.
unique_subquery | This type replaces eq_ref for some IN subqueries
index_subquery | This join type is similar to unique_subquery
range | Only rows that are in a given range are
retrieved, using an index to select the rows
index | The index join type is the same as ALL, except
that the index tree is scanned
ALL | A full table scan is done for each combination
of rows from the previous tables.

Queries where MySQL can use an index:

CREATE TABLE records (
id int unsigned NOT NULL,
a char(2) NOT NULL,
b char(2) NOT NULL,
c char(2) NOT NULL,
PRIMARY KEY (id),
KEY `idx_a_b` (a,b)
)

Find matching rows: WHERE

A query must use the index’s left-most prefix in order to use it. The index definition specifies at least one index column, beginning with the left-most index column. The left-most prefix is necessary because the underlying index structure is ordered by the index column order, and it can only be traversed in that order.

Let’s take a simple example of WHERE.

EXPLAIN SELECT * FROM records WHERE id=1;

Let’s take a more realistic example of WHERE.

EXPLAIN SELECT * from records where id > 1 and id < 5 and c = 'Bh';

Let’s use both leftmost prefixes of the secondary index.

EXPLAIN SELECT * FROM records where a = 'Ab';
EXPLAIN SELECT * FROM records WHERE a = 'Ab' AND b = 'Cd';

Groups rows: GROUP BY

EXPLAIN SELECT a, COUNT(*) FROM records GROUP BY a;

Sort rows: ORDER BY

There are three ways to use an index to optimize ORDER BY.

EXPLAIN SELECT a,b from records ORDER BY id;
EXPLAIN SELECT a, b FROM records WHERE a = 'Hm' ORDER BY b;
EXPLAIN SELECT * FROM records WHERE a = 'Hm' AND b = 'Bh' ORDER BY id;
EXPLAIN SELECT * FROM records WHERE a = 'Hm' ORDER BY id;

Avoid reading rows: Covering Indexes

EXPLAIN SELECT a,b FROM records WHERE a = 'Hm' AND b = 'Bh';

Join table: Joins

CREATE TABLE records_info (
id char(2) NOT NULL,
info varchar(16) DEFAULT NULL,
PRIMARY KEY (id)
)
EXPLAIN SELECT info
FROM records JOIN records_info ON (records.a = records_info.id)
WHERE a IN ('Ab', 'B', 'Hk');

References

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store