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:
|
1 2 3 4 5 6 7 |
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:
|
1 2 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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,
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 |
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:
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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.
Resources
RELATED POSTS
Hi Sveta,
Thanks for a great example of the value of EXPLAIN FORMAT=JSON! I just want to comment that EXPLAIN EXTENDED has been deprecated. Starting with MySQL 5.7, the basic EXPLAIN command will include the extra columns and warnings that you earlier got from EXPLAIN EXTENDED.
And MariaDB will show it as :
“attached_condition”: “(Country.Continent = ‘Asia’)”
that is, without excessive qouting or excessive use of “dbname.” everywhere (dbname is removed if it denotes the current database).
On the topic itself: the problem with attached_condition is that EXPLAIN (in both MariaDB and MySQL) does a lot of transformations before the query is printed back. I think, CREATE VIEW and SHOW CREATE VIEW will do a better job here. These commands will show which table the field is from, without applying any optimizations to the WHERE clause.