Let's start with an example
select City.name as city, Country.name as country,
group_concat(Language)
from City join CountryLanguage using(CountryCode)
join Country
where City.CountryCode=Country.Code and Continent = 'North America'
and District='St George'
group by City.name, Country.Name;
Using traditional Explain Keyword show us
mysql> explain select City.name as city, Country.name as country,
group_concat(Language) from City join CountryLanguage
using(CountryCode) join Country
where City.CountryCode=Country.Code
and Continent = 'North America'
and District='St George' group by City.name, Country.NameG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Country
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 239
filtered: 14.29
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: City
partitions: NULL
type: ref
possible_keys: CountryCode
key: CountryCode
key_len: 3
ref: world.Country.Code
rows: 18
filtered: 10.00
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: CountryLanguage
partitions: NULL
type: ref
possible_keys: PRIMARY,CountryCode
key: CountryCode
key_len: 3
ref: world.Country.Code
rows: 4
filtered: 100.00
Extra: Using index
3 rows in set, 1 warning (0.00 sec)
EXPLAIN FORMAT=JSON will tell us to which columns we should add covered index
mysql> explain format=json select City.name as city, Country.name as country, group_concat(Language) from City join CountryLanguage using(CountryCode) join Country where City.CountryCode=Country.Code and Continent = 'North America' and District='St George' group by City.name, Country.NameG
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "927.92"
},
{
"table": {
"table_name": "City",
"access_type": "ref",
"possible_keys": [
"CountryCode"
],
"key": "CountryCode",
"used_key_parts": [
"CountryCode"
],
"key_length": "3",
"ref": [
"world.Country.Code"
],
"rows_examined_per_scan": 18,
"rows_produced_per_join": 63,
"filtered": "10.00",
"cost_info": {
"read_cost": "630.74",
"eval_cost": "12.61",
"prefix_cost": "810.68",
"data_read_per_join": "4K"
},
"used_columns": [
"ID",
"Name",
"CountryCode",
"District"
],
"attached_condition": "(`world`.`City`.`District` = 'St George')"
}
},
Watch the used columns values.Will use this on covered Index.
mysql> alter table City add index cov(CountryCode, District, Name);
Query OK, 0 rows affected (2.74 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain format=json select City.name as city,
Country.name as country,
group_concat(Language)
from City join CountryLanguage
using(CountryCode) join
Country where City.CountryCode=Country.Code
and Continent = 'North America'
and District='St George' group by City.name, Country.NameG
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "296.28"
},
{
"table": {
"table_name": "City",
"access_type": "ref",
"possible_keys": [
"CountryCode",
"cov"
],
"key": "cov",
"used_key_parts": [
"CountryCode",
"District"
],
"key_length": "23",
"ref": [
"world.Country.Code",
"const"
],
"rows_examined_per_scan": 2,
"rows_produced_per_join": 100,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "34.65",
"eval_cost": "20.19",
"prefix_cost": "108.64",
"data_read_per_join": "7K"
},
"used_columns": [
"ID",
"Name",
"CountryCode",
"District"
]
}
},
Exammine the metrics:
- query_cost – 296.28 for the indexed table against 927.92 (smaller is better)
- rows_examined_per_scan – 2 versus 18 (smaller is better)
- filtered – 100 versus 10 (bigger is better)
- cost_info – read_cost and prefix_cost for the indexed table are smaller than when not indexed, which is better. However, eval_cost and data_read_per_join are bigger. But since we read nine times less rows overall, the cost is still better.
0 comments:
Post a Comment