MySQL Explain Ryan Lv @SAP Jam

Series • Understanding MySQL explain - June • How to design indexes - August

Material and Method • MySQL 5.7.17 • Sakila sample database The Sakila sample database was initially developed by Mike Hillyer, a former member of the MySQL AB documentation team, and is intended to provide a standard schema that can be used for examples in books, tutorials, articles, samples, and so forth. Sakila sample database also serves to highlight the latest features of MySQL such as Views, Stored Procedures, and Triggers. Download and Install https://dev.mysql.com/doc/sakila/en/sakila- installation.html

Intention

• Shall we add indexes? • Are indexes efficient? • What's executation plan of optimizer • Optimize queries

How to understand this report? mysql> explain select * from city where country_id = 103; +----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | city | NULL | ref | idx_fk_country_id | idx_fk_country_id | 2 | const | 35 | 100.00 | NULL | +----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+

Objective • Hunch • Which columns are more important?

1. What does id mean? +----+-------------+-------+------------+------+-------------------+---- | id | select_type | table | partitions | type | possible_keys | ... +----+-------------+-------+------------+------+-------------------+---- | 1 | SIMPLE | city | NULL | ref | idx_fk_country_id | ... +----+-------------+-------+------------+------+-------------------+---- 1 row in set, 1 warning (0.00 sec) > explain select * from city where country_id = 103;

> explain select * from city where country_id = 103; +----+-------------+-------+------------+------+-------------------+---- | id | select_type | table | partitions | type | possible_keys | ... +----+-------------+-------+------------+------+-------------------+---- | 1 | SIMPLE | city | NULL | ref | idx_fk_country_id | ... +----+-------------+-------+------------+------+-------------------+---- 1 row in set, 1 warning (0.00 sec) Case 1

+----+-------------+------------+------------+-------+-.... | id | select_type | table | partitions | type | .... +----+-------------+------------+------------+-------+-.... | 1 | PRIMARY | | NULL | ALL | .... | 2 | DERIVED | city | NULL | index | .... +----+-------------+------------+------------+-------+-.... 2 rows in set, 1 warning (0.00 sec) Case 2 > explain select * from ( select count(*) as city_count from city group by country_id) t where city_count > 10;

2. What does select_type mean?

+----+-------------+-------+------------+------+-------------------+---- | id | select_type | table | partitions | type | possible_keys | ... +----+-------------+-------+------------+------+-------------------+---- | 1 | SIMPLE | city | NULL | ref | idx_fk_country_id | ... +----+-------------+-------+------------+------+-------------------+---- 1 row in set, 1 warning (0.00 sec) 2.1 Simple: NO union; NO subqueries mysql> explain select * from city where country_id = 103;

+----+-------------+------------+------------+-------+-.... | id | select_type | table | partitions | type | .... +----+-------------+------------+------------+-------+-.... | 1 | PRIMARY | | NULL | ALL | .... | 2 | DERIVED | city | NULL | index | .... +----+-------------+------------+------------+-------+-.... 2 rows in set, 1 warning (0.00 sec) > explain select * from ( select count(*) as city_count from city group by country_id) t where city_count > 10; 2.2 Primary: outermost select

+----+-------------+------------+------------+-------+-.... | id | select_type | table | partitions | type | .... +----+-------------+------------+------------+-------+-.... | 1 | PRIMARY | | NULL | ALL | .... | 2 | DERIVED | city | NULL | index | .... +----+-------------+------------+------------+-------+-.... 2 rows in set, 1 warning (0.00 sec) > explain select * from ( select count(*) as city_count from city group by country_id) t where city_count > 10; 2.3 Derived: subquery in From clause

EXPLAIN SELECT actor_id, (SELECT 1 FROM sakila.film_actor WHERE film_actor.actor_id = der_1.actor_id LIMIT 1) FROM ( SELECT actor_id FROM sakila.actor LIMIT 5 ) AS der_1 UNION ALL SELECT film_id, (SELECT @var1 FROM sakila.rental LIMIT 1) FROM ( SELECT film_id, (SELECT 1 FROM sakila.store LIMIT 1) FROM sakila.film LIMIT 5 ) AS der_2; +----+----------------------+------------+------------+-------+-... | id | select_type | table | partitions | type | ... +----+----------------------+------------+------------+-------+-... | 1 | PRIMARY | | NULL | ALL | ... | 3 | DERIVED | actor | NULL | index | ... | 2 | DEPENDENT SUBQUERY | film_actor | NULL | ref | ... | 4 | UNION | | NULL | ALL | ... | 6 | DERIVED | film | NULL | index | ... | 7 | SUBQUERY | store | NULL | index | ... | 5 | UNCACHEABLE SUBQUERY | rental | NULL | index | ... +----+----------------------+------------+------------+-------+-... nonsense query

select type • SIMPLE, simple select • PRIMARY, outermost select • DERIVED, subquery in From clause • UNION, second or later select in a union • SUBQUERY, first select in a subquery • ....

3. What does table mean? mysql> explain select * from city where country_id = 103; +----+-------------+-------+------------+------+-------------------+---- | id | select_type | table | partitions | type | possible_keys | ... +----+-------------+-------+------------+------+-------------------+---- | 1 | SIMPLE | city | NULL | ref | idx_fk_country_id | ... +----+-------------+-------+------------+------+-------------------+---- 1 row in set, 1 warning (0.00 sec) This column shows which table the row is accessing. In most cases, it’s straightforward.

4. What does partitions mean? +----+-------------+-------+------------+------+-------------------+---- | id | select_type | table | partitions | type | possible_keys | ... +----+-------------+-------+------------+------+-------------------+---- | 1 | SIMPLE | city | NULL | ref | idx_fk_country_id | ... +----+-------------+-------+------------+------+-------------------+---- 1 row in set, 1 warning (0.00 sec) mysql> explain select * from city where country_id = 103;

5. What does type mean? +----+-------------+-------+------------+------+-------------------+---- | id | select_type | table | partitions | type | possible_keys | ... +----+-------------+-------+------------+------+-------------------+---- | 1 | SIMPLE | city | NULL | ref | idx_fk_country_id | ... +----+-------------+-------+------------+------+-------------------+---- 1 row in set, 1 warning (0.00 sec) The MySQL manual says this column shows the “join type,” but we think it’s more accurate to say the access type—in other words, how MySQL has decided to find rows in the table. Here are the most important access methods, from worst to best: > explain select * from city where country_id = 103;

5.1 ALL first_name is not indexed

5.1 ALL explain select * from actor where first_name='Ryan';

5.1 ALL explain select * from actor where first_name='Ryan'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 200 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ 1 row in set, 1 warning (0.00 sec)

5.2 Index +----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | actor | NULL | Inex | NULL | NULL | NULL | NULL | 200 | Using filesort| +----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ 1 row in set, 1 warning (0.00 sec) explain select customer_id from rental order by customer_id desc;

CREATE TABLE `city` ( `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `city` varchar(50) NOT NULL, `country_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON U PRIMARY KEY (`city_id`), KEY `idx_fk_country_id` (`country_id`), CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFEREN ) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8; 5.3 Range

+----+-------------+-------+------------+-------+-------------------+-------------------+-... | id | select_type | table | partitions | type | possible_keys | key | ... +----+-------------+-------+------------+-------+-------------------+-------------------+-... | 1 | SIMPLE | city | NULL | range | idx_fk_country_id | idx_fk_country_id | ... +----+-------------+-------+------------+-------+-------------------+-------------------+-... 1 row in set, 1 warning (0.00 sec) 5.3.1 > mysql> explain select * from city where country_id > 100;

+----+-------------+-------+------------+-------+---------------+-... | id | select_type | table | partitions | type | possible_keys | ... +----+-------------+-------+------------+-------+---------------+-... | 1 | SIMPLE | city | NULL | range | PRIMARY | ... +----+-------------+-------+------------+-------+---------------+-... 1 row in set, 1 warning (0.00 sec) 5.3.2 < mysql> explain select * from city where country_id < 100;

+----+-------------+-------+------------+-------+---------------+-... | id | select_type | table | partitions | type | possible_keys | ... +----+-------------+-------+------------+-------+---------------+-... | 1 | SIMPLE | city | NULL | range | PRIMARY | ... +----+-------------+-------+------------+-------+---------------+-... 1 row in set, 1 warning (0.00 sec) 5.3.3 IN mysql> explain select * from city where country_id in (1, 3, 5, 7, 9);

+----+-------------+-------+------------+-------+-------------------+... | id | select_type | table | partitions | type | possible_keys |... +----+-------------+-------+------------+-------+-------------------+... | 1 | SIMPLE | city | NULL | range | idx_fk_country_id |... +----+-------------+-------+------------+-------+-------------------+... 1 row in set, 1 warning (0.02 sec) 5.3.3 between mysql> explain select * from city where country_id between 1 and 9;

use an index to select the rows in given range. 5.3 What's range? ① ②

5.4 ref CREATE TABLE `city` ( `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `city` varchar(50) NOT NULL, `country_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON U PRIMARY KEY (`city_id`), KEY `idx_fk_country_id` (`country_id`), CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFEREN ) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8;

+----+-------+------+-------------------+---------+-------+------+... | id | table | type | key | key_len | ref | rows |... +----+-------+------+-------------------+---------+-------+------+... | 1 | city | ref | idx_fk_country_id | 2 | const | 35 |... +----+-------+------+-------------------+---------+-------+------+... 1 row in set, 1 warning (0.00 sec) 5.4 ref mysql> explain select * from city where country_id = 103;

• multi rows are read from table • secondary key are used. (NOT primary key, NOT unique key) 5.4 Definition of ref From High Performance MySQL 3rd Edition P728 Appendix D. Using Explain

It’s called ref because the index is compared to some reference value. The reference value is either a constant or a value from a previous table in a multiple-table query. 5.4 Why is it called ref? From High Performance MySQL 3rd Edition P728 Appendix D. Using Explain

5.5 eq_ref SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

select city.city, country.country from city join country where city.country_id = country.country_id and city.city_id in (13, 17, 19, 20, 21); +-----------------+--------------+ | city | country | +-----------------+--------------+ | al-Hawiya | Saudi Arabia | | Alessandria | Italy | | Allende | Mexico | | Almirante Brown | Argentina | | Alvorada | Brazil | +-----------------+--------------+ 5 rows in set (0.02 sec)

select city.city, country.country from city join country where city.country_id = country.country_id and city.city_id in (13, 17, 19, 20, 21); +-----------------+--------------+ | city | country | +-----------------+--------------+ | al-Hawiya | Saudi Arabia | | Alessandria | Italy | | Allende | Mexico | | Almirante Brown | Argentina | | Alvorada | Brazil | +-----------------+--------------+ 5 rows in set (0.02 sec) Step1

+-----------------+--------------+ | city | country | +-----------------+--------------+ | al-Hawiya | Saudi Arabia | | Alessandria | Italy | | Allende | Mexico | | Almirante Brown | Argentina | | Alvorada | Brazil | +-----------------+--------------+ 5 rows in set (0.02 sec) Step 2 select city.city, country.country from city join country where city.country_id = country.country_id and city.city_id in (13, 17, 19, 20, 21); [6, 15, 49, 60, 82]

5.6 Const SELECT * FROM city where [primay_key]=101 SELECT * FROM city where [unique_not_null_key]='rrG7d1D4YUO'

5.6 e.g. SELECT * FROM users where id=101 SELECT * FROM users where uuid = 'rrG7d1D4YUO' note: These sql statements are made up, has nothing to do with sakila

Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. 5.6 why is it called constant?

select * from city join country on city.country_id = country.country_id where country.uuid = "EnuZl33x"; note: These sql statements are made up, has nothing to do with sakila /* step 1 */ select country_id from country where uuid = "EnuZl33x" => 3 /* step 2 */ select * from city join country on city.country_id = 3

6. What does possible_keys mean? > explain select * from film_actor where film_id = 3 or actor_id = 3; +----+...+------------------------+------------------------+-... | id |...| possible_keys | key | ... +----+...+------------------------+------------------------+-... | 1 |...| PRIMARY,idx_fk_film_id | idx_fk_film_id,PRIMARY | ... +----+...+------------------------+------------------------+-... 1 row in set, 1 warning (0.00 sec)

7. What does key mean? > explain select * from film_actor where film_id = 3 or actor_id = 3; +----+...+------------------------+------------------------+-... | id |...| possible_keys | key | ... +----+...+------------------------+------------------------+-... | 1 |...| PRIMARY,idx_fk_film_id | idx_fk_film_id,PRIMARY | ... +----+...+------------------------+------------------------+-... 1 row in set, 1 warning (0.00 sec)

8. What does key_len mean? SKIP

9. What does ref mean? mysql> explain select * from city where country_id = 103; +----+-------------+-------....+---------+-------+------+----------+-------+ | id | select_type | table ....| key_len | ref | rows | filtered | Extra | +----+-------------+-------....+---------+-------+------+----------+-------+ | 1 | SIMPLE | city ....| 2 | const | 35 | 100.00 | NULL | +----+-------------+-------....+---------+-------+------+----------+-------+ 103

10. What does rows mean? > explain select * from city where country_id = 103; +----+-...---------+-------+------+... | id | ... key_len | ref | rows |... +----+-...---------+-------+------+... | 1 | ... 2 | const | 35 |... +----+-...---------+-------+------+...

• the number of rows MySQL believes it must examine to execute the query. • this number is an estimate, and may not always be exact.

11. What does filtered mean?

The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables.

last_name is indexed

select * from actor where last_name = 'ALLEN' and first_name = 'CUBA'; [MERYl ALLEN, KIM ALLEN, CUBA ALEEN] [CUBA ALEEN] [MERYl ALLEN, KIM ALLEN, CUBA ALEEN]

12. What does extra mean? +----+-...---------+-------+------+----------+-------+ | id | ... key_len | ref | rows | filtered | Extra | +----+-...---------+-------+------+----------+-------+ | 1 | ... 2 | const | 35 | 100.00 | NULL | +----+-...---------+-------+------+----------+-------+ > explain select * from city where country_id = 103;

12. Extra • Covering index query • Using filesort • Using where • Using temporary • Using index condition

Which columns are more important? Column JSON Name Meaning id select_id The SELECT identifier select_type None The SELECT type table table_name The table for the output row partitions partitions The matching partitions type access_type The join type possible_keys possible_keys The possible indexes to choose key key The index actually chosen key_len key_length The length of the chosen key ref ref The columns compared to the index rows rows Estimate of rows to be examined filtered filtered Percentage of rows filtered by table condition Extra None Additional information

Column JSON Name Meaning id select_id select_type None table table_name partitions partitions type access_type constant / eq_ref / ref / range / index / all possible_keys possible_keys key key key_len key_length ref ref rows rows filtered filtered Extra None

Q&A

Reference • https://www.sitepoint.com/using-explain-to-write- better-mysql-queries/ • https://dev.mysql.com/doc/refman/5.5/en/explain- output.html