CTEs and Views

Views are a way of saving a query for later reuse, and they appear to applications as if they are a table. This allows complex SQL queries to be broken down and simplified in stages. CTEs themselves are very similar to views, the difference being that they have a shorter lifetime, being tied to a single-statement only.

The MySQL Optimizer has two main strategies for executing CTEs and VIEWs:

1. Merge. Transform the query so that the definition of the CTE or VIEW is merged in with the remainder of the query. It is possible to see the result of the merging by running SHOW WARNINGS after EXPLAIN.
2. Materialize. Execute the CTE or VIEW and store the results in a temporary table. The remainder of the query will be performed against the temporary table. The materialize option is usually the slower method, and chosen when the merge option is not suitable. There are exceptions to this, where early materialization can shortcut work and lead to faster execution.

Example 21: A query on a view being merged

CREATE VIEW vCountry_Asia AS SELECT * FROM Country WHERE Continent='Asia';
EXPLAIN FORMAT=JSON
SELECT * FROM vCountry_Asia WHERE Name='China';
{
 "query_block": {
   "select_id": 1,
   "cost_info": {
     "query_cost": "1.20"
   },
   "table": {
     "table_name": "Country",  # Table name is the base table name
     "access_type": "ref",
     "possible_keys": [
       "c",
       "c_p",
       "Name"
     ],
     "key": "Name",
     "used_key_parts": [
       "Name",
       "Continent"
     ],
     "key_length": "53",
     "ref": [
       "const",
       "const"
     ],
     "rows_examined_per_scan": 1,
     "rows_produced_per_join": 1,
     "filtered": "100.00",
     "cost_info": {
       "read_cost": "1.00",
       "eval_cost": "0.20",
       "prefix_cost": "1.20",
       "data_read_per_join": "264"
     },
     "used_columns": [
       "Code",
       "Name",
       "Continent",
       "Region",
       "SurfaceArea",
       "IndepYear",
       "Population",
       "LifeExpectancy",
       "GNP",
       "GNPOld",
       "LocalName",
       "GovernmentForm",
       "HeadOfState",
       "Capital",
       "Code2"
     ]
   }
 }
}

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`.`Continent` = 'Asia') and (`world`.`Country`.`Name` = 'China'))

Example 22: A query on a view cannot merge

CREATE VIEW vCountrys_Per_Continent AS SELECT Continent, COUNT(*) as Count FROM Country
GROUP BY Continent;
EXPLAIN FORMAT=JSON
SELECT * FROM vCountrys_Per_Continent WHERE Continent='Asia';
{
 "query_block": {
   "select_id": 1,
   "cost_info": {
     "query_cost": "12.47"
   },
   "table": {
     "table_name": "vCountrys_Per_Continent",  # Table name is the view name
     "access_type": "ref",
     "possible_keys": [
       "<auto_key0>"
     ],
     "key": "<auto_key0>",
     "used_key_parts": [
       "Continent"
     ],
     "key_length": "1",
     "ref": [
       "const"
     ],
     "rows_examined_per_scan": 10,
     "rows_produced_per_join": 10,
     "filtered": "100.00",
     "cost_info": {
       "read_cost": "10.39",
       "eval_cost": "2.08",
       "prefix_cost": "12.47",
       "data_read_per_join": "166"
     },
     "used_columns": [
       "Continent",
       "Count"
     ],
     "materialized_from_subquery": {
       "using_temporary_table": true,
       "dependent": false,
       "cacheable": true,
       "query_block": {
         "select_id": 2,
         "cost_info": {
           "query_cost": "56.80"
         },
         "grouping_operation": {
           "using_filesort": false,
           "table": {
             "table_name": "Country",
             "access_type": "index",
             "possible_keys": [
               "PRIMARY",
               "p",
               "c",
               "p_c",
               "c_p",
               "Name"
             ],
             "key": "c",
             "used_key_parts": [
               "Continent"
             ],
             "key_length": "1",
             "rows_examined_per_scan": 239,
             "rows_produced_per_join": 239,
             "filtered": "100.00",
             "using_index": true,
             "cost_info": {
               "read_cost": "9.00",
               "eval_cost": "47.80",
               "prefix_cost": "56.80",
               "data_read_per_join": "61K"
             },
             "used_columns": [
               "Code",
               "Continent"
             ]
           }
         }
       }
     }
   }
 }
}

SHOW WARNINGS;
/* select#1 */ select `vCountrys_Per_Continent`.`Continent` AS `Continent`,`vCountrys_Per_Continent`.`Count` AS `Count` from `world`.`vCountrys_Per_Continent` where (`vCountrys_Per_Continent`.`Continent` = 'Asia')