When you work with complicated queries, especially ones which contain subqueries, it is easy to make a typo or misinterpret column name. While in many cases you will receive a
column not found
error, sometimes you can get strange results instead.
Like finding 4079 countries in Antarctica:
mysql> select count(*) from City where CountryCode in (select CountryCode from Country where Continent = 'Antarctica'); +----------+ | count(*) | +----------+ | 4079 | +----------+ 1 row in set (0.05 sec)
Or not finding any cities in Georgia:
mysql> select Name, Language from City join CountryLanguage using (CountryCode) where CountryCode in (select Code from Country where District='Georgia' and Continent='Asia'); Empty set (0.18 sec)
I used a standard world database for these examples.
The reason for both errors is misplacing the column names in the two tables. While it may look simple when you already know what is wrong, I’ve seen support tickets where users were puzzled by simple queries like these.
I always recommended my customers examine their queries, and use
EXPLAIN EXTENDED
, followed by
SHOW WARNINGS
. Then thoroughly examine resulting query.
For example, for the query result mentioned above, we can see something like this:
mysql> W Show warnings enabled. mysql> explain extended select count(*) from City where CountryCode in (select CountryCode from Country where Continent = 'Antarctica'); +----+--------------------+---------+-------+---------------+-------------+---------+------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+---------+-------+---------------+-------------+---------+------+------+----------+--------------------------+ | 1 | PRIMARY | City | index | NULL | CountryCode | 3 | NULL | 4005 | 100.00 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | Country | ALL | NULL | NULL | NULL | NULL | 227 | 100.00 | Using where | +----+--------------------+---------+-------+---------------+-------------+---------+------+------+----------+--------------------------+ 2 rows in set, 2 warnings (0.01 sec) Note (Code 1276): Field or reference 'world.City.CountryCode' of SELECT #2 was resolved in SELECT #1 Note (Code 1003): select count(0) AS `count(*)` from `world`.`City` where <in_optimizer>(`world`.`City`.`CountryCode`,<exists>(select 1 from `world`.`Country` where ((`world`.`Country`.`Continent` = 'Antarctica') and (<cache>(`world`.`City`.`CountryCode`) = `world`.`City`.`CountryCode`))))
Note
(Code 1276): Field or reference 'world.City.CountryCode' of SELECT #2 was resolved in SELECT #1
tells us what is wrong, but without knowledge of the table definition it is hard to find out why
SELECT #2 was resolved in SELECT #1
. It also doesn’t give us much information if
SELECT #1
uses more than one table in JOIN. For example,
mysql> explain extended select Name, Language from City join CountryLanguage using (CountryCode) where CountryCode in (select Code from Country where District='Georgia' and Continent='Asia'); +----+--------------------+-----------------+-----------------+---------------------+-------------+---------+-----------------------------------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-----------------+-----------------+---------------------+-------------+---------+-----------------------------------+------+----------+-------------+ | 1 | PRIMARY | CountryLanguage | index | PRIMARY,CountryCode | CountryCode | 3 | NULL | 1157 | 100.00 | Using index | | 1 | PRIMARY | City | ref | CountryCode | CountryCode | 3 | world.CountryLanguage.CountryCode | 1 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | Country | unique_subquery | PRIMARY | PRIMARY | 3 | func | 1 | 100.00 | Using where | +----+--------------------+-----------------+-----------------+---------------------+-------------+---------+-----------------------------------+------+----------+-------------+ 3 rows in set, 2 warnings (0.00 sec) Note (Code 1276): Field or reference 'world.City.District' of SELECT #2 was resolved in SELECT #1 Note (Code 1003): select `world`.`City`.`Name` AS `Name`,`world`.`CountryLanguage`.`Language` AS `Language` from `world`.`City` join `world`.`CountryLanguage` where ((`world`.`City`.`CountryCode` = `world`.`CountryLanguage`.`CountryCode`) and <in_optimizer>(`world`.`City`.`CountryCode`,<exists>(<primary_index_lookup>(<cache>(`world`.`City`.`CountryCode`) in Country on PRIMARY where ((`world`.`Country`.`Continent` = 'Asia') and (`world`.`City`.`District` = 'Georgia') and (<cache>(`world`.`City`.`CountryCode`) = `world`.`Country`.`Code`))))))
It is not clear if
world.City.District
was resolved from table
City
or
CountryLanguage
.
EXPLAIN FORMAT=JSON
gives us this information.
mysql> explain format=json select Name, Language from City join CountryLanguage using (CountryCode) where CountryCode in (select Code from Country where District='Georgia' and Continent='Asia')G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "907.97"
},
"nested_loop": [
{
"table": {
"table_name": "Country",
"access_type": "ALL",
"possible_keys": [
"PRIMARY"
],
"rows_examined_per_scan": 239,
"rows_produced_per_join": 34,
"filtered": "14.29",
"cost_info": {
"read_cost": "46.97",
"eval_cost": "6.83",
"prefix_cost": "53.80",
"data_read_per_join": "8K"
},
"used_columns": [
"Code",
"Continent"
],
"attached_condition": "(`world`.`Country`.`Continent` = 'Asia')"
}
},
{
"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": 61,
"filtered": "10.00",
"cost_info": {
"read_cost": "616.34",
"eval_cost": "12.33",
"prefix_cost": "793.40",
"data_read_per_join": "4K"
},
"used_columns": [
"Name",
"CountryCode",
"District"
],
"attached_condition": "(`world`.`City`.`District` = 'Georgia')"
}
},
{
"table": {
"table_name": "CountryLanguage",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"CountryCode"
],
"key": "CountryCode",
"used_key_parts": [
"CountryCode"
],
"key_length": "3",
"ref": [
"world.Country.Code"
],
"rows_examined_per_scan": 4,
"rows_produced_per_join": 260,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "62.51",
"eval_cost": "52.06",
"prefix_cost": "907.97",
"data_read_per_join": "10K"
},
"used_columns": [
"CountryCode",
"Language"
]
}
}
]
}
}
1 row in set, 2 warnings (0.00 sec)
Note (Code 1276): Field or reference 'world.City.District' of SELECT #2 was resolved in SELECT #1
Note (Code 1003): /* select#1 */ select `world`.`City`.`Name` AS `Name`,`world`.`CountryLanguage`.`Language` AS `Language` from `world`.`Country` join `world`.`City` join `world`.`CountryLanguage` where ((`world`.`City`.`CountryCode` = `world`.`Country`.`Code`) and (`world`.`CountryLanguage`.`CountryCode` = `world`.`Country`.`Code`) and (`world`.`Country`.`Continent` = 'Asia') and (`world`.`City`.`District` = 'Georgia'))
All the details are in the
attached_condition
element, which contains part of the
WHERE
clause, attached to this particular table.
Unfortunately the
EXPLAIN
output cannot be saved in a variable, which can then later be proceeded by JSON functions, but we can set a pager to filter the
table_name
and
attached_condition
keywords:
mysql> pager egrep 'table_name|attached_condition'
PAGER set to 'egrep 'table_name|attached_condition''
mysql> explain format=json select Name, Language from City join CountryLanguage using (CountryCode) where CountryCode in (select Code from Country where District='Georgia' and Continent='Asia')G
"table_name": "Country",
"attached_condition": "(`world`.`Country`.`Continent` = 'Asia')"
"table_name": "City",
"attached_condition": "(`world`.`City`.`District` = 'Georgia')"
"table_name": "CountryLanguage",
1 row in set, 2 warnings (0.00 sec)
Knowing that JSON structure is hierarchical,we clearly see what condition
(`world`.`City`.`District` = 'Georgia')
was resolved from table
City
while condition
(`world`.`Country`.`Continent` = 'Asia')
belongs to table
Country
. We can conclude that table
Country
probably does not have a field named
District
, while table
City
does. The
SHOW CREATE TABLE
statements confirm this guess:
mysql> show create table CountryG
*************************** 1. row ***************************
Table: Country
Create Table: CREATE TABLE `Country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint(6) DEFAULT NULL,
`Population` int(11) NOT NULL DEFAULT '0',
`LifeExpectancy` float(3,1) DEFAULT NULL,
`GNP` float(10,2) DEFAULT NULL,
`GNPOld` float(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int(11) DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.05 sec)
mysql> show create table CityG
*************************** 1. row ***************************
Table: City
Create Table: CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
This is the reason why the field
District
was resolved from the table
City
, and not
Country.
Conclusion:
EXPLAIN FORMAT=JSON
can help you find if the column names are mixed up in the query, even without knowledge of the table definitions.
The post attached_condition: How EXPLAIN FORMAT=JSON can spell-check your queries appeared first on MySQL Performance Blog.