Slide 47
Slide 47 text
Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
EXPLAIN output changes in MySQL 8.0
• EXPLAIN FORMAT=JSON and TREE provide add details on
Used indexes, Pushed index conditions, Cost estimates,
Data estimates
47
mysql> EXPLAIN FORMAT=JSON SELECT city.name,
city.population, country.name AS country
FROM city, country
WHERE city.CountryCode=country.Code
AND city.name='Roma';
+-------------------------------------+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "550.97"
},
"nested_loop": [
{
"table": {
"table_name": "city",
"access_type": "ALL",
"possible_keys": [
"CountryCode"
],
"rows_examined_per_scan": 4035,
"rows_produced_per_join": 403,
"filtered": "10.00",
"cost_info": {
"read_cost": "369.40",
"eval_cost": "40.35",
"prefix_cost": "409.75",
"data_read_per_join": "97K"
},
...
mysql> EXPLAIN FORMAT=TREE SELECT city.name, city.population, country.name AS country
FROM city, country
WHERE city.CountryCode=country.Code AND city.name='Roma';
+---------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=550.98 rows=404)
-> Filter: (city.`Name` = 'Roma') (cost=409.75 rows=404)
-> Table scan on city (cost=409.75 rows=4035)
-> Single-row index lookup on country using PRIMARY (Code=city.CountryCode) (cost=0.25 rows=1)
|
+---------------------------------------------------------------------------------------------------+
mysql> EXPLAIN SELECT city.name, city.population, country.name AS country FROM city, country WHERE city.CountryCode=country.Code AND city.name='Roma';
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | ALL | CountryCode | NULL | NULL | NULL | 4035 | 10 | Using where |
| 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 12 | world.city.CountryCode | 1 | 100 | NULL |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+