Logical Transformations¶
The MySQL optimizer may make changes to your query in ways that do not affect the results. The goal behind these transformations is to try and rewrite the query in a way that it can eliminate work, and execute queries faster. For example, consider the query:
SELECT * FROM Country
WHERE population > 5000000 AND continent='Asia' AND 1=1;
Because 1 always equals 1, and this condition is AND (not OR), it is completely redundant. There is no benefit from checking that 1 still equals 1 on each row while executing the query, and removing the condition will still return the same results. We can see MySQL applying this transformation in OPTIMIZER_TRACE, as well as a number of other transformations:
..
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`Country`.`Population` > 5000000) and (1 = 1))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`Country`.`Population` > 5000000) and (1 = 1))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`Country`.`Population` > 5000000) and (1 = 1))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`Country`.`Population` > 5000000)"
}
]
}
},
..
The rewritten form of a query after all transformations have been applied is also available in SHOW WARNINGS after EXPLAIN has been executed. The rewritten form of the statement above is as follows:
EXPLAIN FORMAT=JSON SELECT * FROM Country WHERE population > 5000000 AND 1=1;
SHOW WARNINGS;
/* select#1 */ select `world`.`Country`.`Code` AS `Code`,`world`.`Country`.`Name` AS `Name`,`world`.`Country`.`Continent` AS `Continent`,`world`.`Country`.`Region` AS `Region`,`world`.`Country`.`SurfaceArea` AS `SurfaceArea`,`world`.`Country`.`IndepYear` AS `IndepYear`,`world`.`Country`.`Population` AS `Population`,`world`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,`world`.`Country`.`GNP` AS `GNP`,`world`.`Country`.`GNPOld` AS `GNPOld`,`world`.`Country`.`LocalName` AS `LocalName`,`world`.`Country`.`GovernmentForm` AS `GovernmentForm`,`world`.`Country`.`HeadOfState` AS `HeadOfState`,`world`.`Country`.`Capital` AS `Capital`,`world`.`Country`.`Code2` AS `Code2` from `world`.`Country` where (`world`.`Country`.`Population` > 5000000)
Example Transformations¶
Below are a number of examples of queries that have been logically transformed. Note that in several examples PRIMARY and UNIQUE indexes are converted and permanently transformed before the execution phase of a query. Since this portion of the query should already be in its most efficient form, the optimizer is performing a permanent transformation before it considers execution plans. It is another optimization being applied in order to reduce the number of plans that need to be considered.
Code is a primary key. The query is rewritten with values converted to constants.
| Original Form | SELECT * FROM Country WHERE code='CAN' |
| Rewritten Form | /* select#1 */ select 'CAN' AS `Code`,'Canada' AS `Name`,'North America' AS `Continent`,
'North America' AS `Region`,'9970610.00' AS `SurfaceArea`,'1867' AS `IndepYear`,
'31147000' AS `Population`,'79.4' AS `LifeExpectancy`,'598862.00' AS `GNP`,'625626.00' AS `GNPOld`,
'Canada' AS `LocalName`,'Constitutional Monarchy, Federation' AS `GovernmentForm`,
'Elisabeth II' AS `HeadOfState`,'1822' AS `Capital`,'CA' AS `Code2` from `world`.`Country` where 1 |
Code is a primary key, but the primary key does not exist in the table (impossible where).
| Original Form | SELECT * FROM Country WHERE code='XYZ' |
| Rewritten Form | /* select#1 */ select NULL AS `Code`,NULL AS `Name`,NULL AS `Continent`,NULL AS `Region`,
NULL AS `SurfaceArea`,NULL AS `IndepYear`,NULL AS `Population`,NULL AS `LifeExpectancy`,NULL AS `GNP`,
NULL AS `GNPOld`,NULL AS `LocalName`,NULL AS `GovernmentForm`,NULL AS `HeadOfState`,NULL AS `Capital`,
NULL AS `Code2` from `world`.`Country` where multiple equal('XYZ', NULL) |
Another form of impossible where. The code exists, but 1=0 is an impossible condition
| Original Form | SELECT * FROM Country WHERE code='CAN' AND 1=0 |
| Rewritten Form | /* select#1 */ select `world`.`Country`.`Code` AS `Code`,`world`.`Country`.`Name` AS `Name`,
`world`.`Country`.`Continent` AS `Continent`,`world`.`Country`.`Region` AS `Region`,
`world`.`Country`.`SurfaceArea` AS `SurfaceArea`,`world`.`Country`.`IndepYear` AS `IndepYear`,
`world`.`Country`.`Population` AS `Population`,`world`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,
`world`.`Country`.`GNP` AS `GNP`,`world`.`Country`.`GNPOld` AS `GNPOld`,
`world`.`Country`.`LocalName` AS `LocalName`,`world`.`Country`.`GovernmentForm` AS `GovernmentForm`,
`world`.`Country`.`HeadOfState` AS `HeadOfState`,`world`.`Country`.`Capital` AS `Capital`,
`world`.`Country`.`Code2` AS `Code2` from `world`.`Country` where 0 |
The derived table subquery is merged back into a join directly onto the Country table
| Original Form | SELECT City.* FROM City, (SELECT * FROM Country WHERE continent='Asia') as Country WHERE
Country.code=City.CountryCode AND Country.population > 5000000; |
| Rewritten Form [1] | /* select#1 */ select `world`.`City`.`ID` AS `ID`,`world`.`City`.`Name` AS `Name`,
`world`.`City`.`CountryCode` AS `CountryCode`,`world`.`City`.`District` AS `District`,
`world`.`City`.`Population` AS `Population` from `world`.`City` join `world`.`Country`
where ((`world`.`Country`.`Continent` = 'Asia') and (`world`.`City`.`CountryCode` = `
world`.`Country`.`Code`) and (`world`.`Country`.`Population` > 5000000)) |
The definition of a view is merged with the predicates used to query it.
| Original Form | CREATE VIEW Countries_in_asia AS SELECT * FROM Country WHERE continent='Asia';
SELECT * FROM Countries_in_asia WHERE population > 5000000; |
| Rewritten Form | /* select#1 */ select `world`.`Country`.`Code` AS `Code`,`world`.`Country`.`Name` AS `Name`,
`world`.`Country`.`Continent` AS `Continent`,`world`.`Country`.`Region` AS `Region`,
`world`.`Country`.`SurfaceArea` AS `SurfaceArea`,`world`.`Country`.`IndepYear` AS `IndepYear`,
`world`.`Country`.`Population` AS `Population`,`world`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,
`world`.`Country`.`GNP` AS `GNP`,`world`.`Country`.`GNPOld` AS `GNPOld`,
`world`.`Country`.`LocalName` AS `LocalName`,`world`.`Country`.`GovernmentForm` AS `GovernmentForm`,
`world`.`Country`.`HeadOfState` AS `HeadOfState`,`world`.`Country`.`Capital` AS `Capital`,
`world`.`Country`.`Code2` AS `Code2` from `world`.`Country`
where ((`world`.`Country`.`Continent` = 'Asia') and (`world`.`Country`.`Population` > 5000000)) |
| [1] | This behavior is dependent on the optimizer_switch derived_merge (on by default) |