Sluggish query? Look at the keys

from the Artful MySQL Tips List

Here is a simple demonstration of the importance of keys in query performance. The example uses three tables from the Sakila database (downloadable from https://dev.mysql.com/doc/sakila/en/sakila-installation.html)--customer with a primary key and about 700 rows, and rental and payment, each with a primary key, a foreign key referencing customer, and about 16,000 rows. The query just counts joined customer, rental and payment rows:

SELECT COUNT(*)
FROM customer c
JOIN rental  r USING (customer_id)
JOIN payment p USING (customer_id);

First, turn off caching so we can see the effects of indexes by themselves:

set query_cache_type=0;
set query_cache_size=0;
reset query cache;

The query with a SQL_NO_CACHE qualifier finishes in three-quarters of a second:

SELECT SQL_NO_CACHE COUNT(*) AS JoinResultRowsNoKeys
FROM customer c
JOIN rental   r USING (customer_id)
JOIN payment  p USING (customer_id);
+-----------------------+
| JoinResultRowsAllKeys |
+-----------------------+
|                445505 |
+-----------------------+
1 row in set (0.47 sec)

Here is EXPLAIN output for the query:

+----+-------------+-------+-------+--------------------+--------------------+---------+----------------------+------+-------------+
| id | select_type | table | type  | possible_keys      | key                | key_len | ref                  | rows | Extra       |
+----+-------------+-------+-------+--------------------+--------------------+---------+----------------------+------+-------------+
|  1 | SIMPLE      | c     | index | PRIMARY            | idx_fk_store_id    | 1       | NULL                 |  541 | Using index |
|  1 | SIMPLE      | r     | ref   | idx_fk_customer_id | idx_fk_customer_id | 2       | sakila.c.customer_id |   13 | Using index |
|  1 | SIMPLE      | p     | ref   | idx_fk_customer_id | idx_fk_customer_id | 2       | sakila.c.customer_id |   14 | Using index |
+----+-------------+-------+-------+--------------------+--------------------+---------+----------------------+------+-------------+

Multiplying the rowcounts for each table EXPLAIN lists gives a rough measure of how many rows the query engine will have to examine: 541*13*14, or 98,462. Now make copies of those tables in another database, with no indexes at all, and run the same query on the new raw tables:

drop schema if exists saktest;
create schema saktest;
use saktest;
drop table if exists customer,rental,payment;
create table customer engine=innodb select * from sakila.customer;
create table rental   engine=innodb select * from sakila.rental;
create table payment  engine=innodb select * from sakila.payment;
SELECT SQL_NO_CACHE COUNT(*) AS JoinResultRowsNoKeys
FROM customer c
JOIN rental  r USING (customer_id)
JOIN payment p USING (customer_id);
+----------------------+
| JoinResultRowsNoKeys |
+----------------------+
|               445505 |
+----------------------+
1 row in set (23.34 sec)

It's almost 50 times slower. EXPLAIN shows why:

+----+-------------+-------+------+---------------+------+---------+------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+-------+--------------------------------+
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |   637 |                                |
|  1 | SIMPLE      | r     | ALL  | NULL          | NULL | NULL    | NULL | 16130 | Using where; Using join buffer |
|  1 | SIMPLE      | p     | ALL  | NULL          | NULL | NULL    | NULL | 16272 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+-------+--------------------------------+

Without indexes, it must examine 637*16130*16272=167,191,708,320 rows, more than a million-and-a-half times more row comparisons than the same query with indexes. We could take the fact that it's only 50 times slower as a tribute to the query engine. If your query is sluggish, look at the keys.

Last updated 17 Apr 2024

Return to the Artful MySQL Tips page