Home

Performance analysis

As database tables grow into hundreds-of-thousands or millions of rows, simple SELECT queries might begin to take significant time, and JOINs will take even longer. In many cases, we can recover high performance by making a small effort to add indexes and analyze our queries for any surprising behavior.

Adding indexes

Most performance issues are the result of querying or joining on columns that do no have indexes. The rule of thumb is to add an index for any column that will be mentioned in a WHERE or JOIN … ON clause. Refer to the indexes notes for details.

EXPLAIN queries

The EXPLAIN feature shows how MySQL processes a SELECT query. It does not run the query, rather it shows some details about how it plans to run the query.

EXPLAIN SELECT ...

Here is a small example on the cinf201_imdb_noindex database:

mysql> explain select * from actors;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | actors | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3754702 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------+

Here is an explanation of the various columns:

An example with JOINS

First, an example with no index, from the database cinf201_imdb_noindexes.

mysql> explain select fname, lname from actors join acted_in on acted_in.idactors = actors.idactors where billing_position = 1;
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                              |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
|  1 | SIMPLE      | acted_in | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 26913770 |    10.00 | Using where                                        |
|  1 | SIMPLE      | actors   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  3750380 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+

This result tells use that no keys (indexes) were used. Also, even though our query lists actors first in the SELECT, we’re really selecting all rows from acted_in, and for each such row, grabbing the actor’s first and last name. We should have as many rows for output as there are acted_in rows where billing position is 1. The EXPLAIN query shows that all rows of the actors table will be examined, which is not strictly necessary.

Compare with the output from indexed tables (using database cinf201_imdb):

mysql> explain select fname, lname from actors join acted_in on acted_in.idactors = actors.idactors where billing_position = 1;
+----+-------------+----------+------------+--------+-------------------------+---------+---------+--------------------------------+----------+----------+-------------+
| id | select_type | table    | partitions | type   | possible_keys           | key     | key_len | ref                            | rows     | filtered | Extra       |
+----+-------------+----------+------------+--------+-------------------------+---------+---------+--------------------------------+----------+----------+-------------+
|  1 | SIMPLE      | acted_in | NULL       | ALL    | ACTED_IN_IDACTORS_INDEX | NULL    | NULL    | NULL                           | 24924220 |    10.00 | Using where |
|  1 | SIMPLE      | actors   | NULL       | eq_ref | PRIMARY                 | PRIMARY | 4       | cinf201_imdb.acted_in.idactors |        1 |   100.00 | NULL        |
+----+-------------+----------+------------+--------+-------------------------+---------+---------+--------------------------------+----------+----------+-------------+

We see now that the primary key (idactors) in the actors table is used to join with the acted_in table, so for each acted_in row, exactly one actors row is retrieved (rather than scanning them all).

In this last EXPLAIN query, the acted_in table didn’t have an index on the billing_position column. Thus, the EXPLAIN query shows “Using where” in the Extra column. We can get faster query execution by addding an index on billing_position. Notice the resulting EXPLAIN query now uses a key for the acted_in table as well:

mysql> explain select fname, lname from actors join acted_in on acted_in.idactors = actors.idactors where billing_position = 1;
+----+-------------+----------+------------+--------+------------------------------------------+------------------+---------+--------------------------------+---------+----------+-------+
| id | select_type | table    | partitions | type   | possible_keys                            | key              | key_len | ref                            | rows    | filtered | Extra |
+----+-------------+----------+------------+--------+------------------------------------------+------------------+---------+--------------------------------+---------+----------+-------+
|  1 | SIMPLE      | acted_in | NULL       | ref    | ACTED_IN_IDACTORS_INDEX,billing_position | billing_position | 5       | const                          | 2518866 |   100.00 | NULL  |
|  1 | SIMPLE      | actors   | NULL       | eq_ref | PRIMARY                                  | PRIMARY          | 4       | cinf201_imdb.acted_in.idactors |       1 |   100.00 | NULL  |
+----+-------------+----------+------------+--------+------------------------------------------+------------------+---------+--------------------------------+---------+----------+-------+
CINF 201 material by Joshua Eckroth is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License. Source code for this website available at GitHub.