EXPLAIN Statements Using Views

EXPLAIN displays the tables that a view references, not the name of the view itself. This is because views are only virtual tables and do not store any data themselves. The definition of the view and the rest of the statement are merged together during SQL optimization.

Example

We have prepared a simple example to help you understand the execution plan for interpreting the VIEW using EXPLAIN.

  1. > drop table if exists t1;
  2. > create table t1 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime);
  3. > insert into t1 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11');
  4. > insert into t1 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11');
  5. > insert into t1 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11');
  6. > insert into t1 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11');
  7. > insert into t1 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11');
  8. > insert into t1 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11');
  9. > insert into t1 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11');
  10. > insert into t1 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11');
  11. > insert into t1 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11');
  13. > drop table if exists t2;
  14. > create table t2 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime);
  15. > insert into t2 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11');
  16. > insert into t2 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11');
  17. > insert into t2 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11');
  18. > insert into t2 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11');
  19. > insert into t2 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11');
  20. > insert into t2 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11');
  21. > insert into t2 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11');
  22. > insert into t2 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11');
  23. > insert into t2 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11');
  25. > create view v1 as select * from (select * from t1) sub where id > 4;
  26. > select * from v1;
  27. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
  28. | id   | ti   | si   | bi   | fl       | dl      | de   | ch           | vch            | dd         | dt                  |
  29. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
  30. |    5 |    1 |    2 |    6 |    51.26 |    5126 |   51 | byebye       |  is subquery?  | 2022-04-28 | 2022-04-28 22:40:11 |
  31. |    6 |    3 |    2 |    1 |    632.1 |    6321 |  632 | good night   | maybe subquery | 2022-04-28 | 2022-04-28 22:40:11 |
  32. |    7 |    4 |    4 |    3 |  7443.11 |  744311 | 7443 | yes          | subquery       | 2022-04-28 | 2022-04-28 22:40:11 |
  33. |    8 |    7 |    5 |    8 |     8758 |  875800 | 8758 | nice to meet | just subquery  | 2022-04-28 | 2022-04-28 22:40:11 |
  34. |    9 |    8 |    4 |    9 | 9849.312 | 9849312 | 9849 | see you      | subquery       | 2022-04-28 | 2022-04-28 22:40:11 |
  35. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
  36. 5 rows in set (0.01 sec)

As shown in the above example, a new VIEW named v1 is created, then query the result of v1. So let’s learn the query plan in this VIEW:

  1. > explain select * from v1;
  2. +--------------------------------------------------------------+
  3. | QUERY PLAN                                                   |
  4. +--------------------------------------------------------------+
  5. | Project                                                      |
  6. |   ->  Project                                                |
  7. |         ->  Project                                          |
  8. |               ->  Table Scan on db1.t1                       |
  9. |                     Filter Cond: (CAST(t1.id AS BIGINT) > 4) |
  10. +--------------------------------------------------------------+
  11. 5 rows in set (0.00 sec)

You can see that Project is the parent node in the execution order of this query. It starts from the most indented child node, then “flows into” its upper parent node, and finally “flow into” the Project parent node.

To execute firstly:

  • Filter Cond: the filter condition

To execute secondly:

  • Table Scan: scans all tables

The following query is executed similarly to the above:

  1. > explain select * from (select * from t1) sub where id > 4;
  2. +--------------------------------------------------------+
  3. | QUERY PLAN                                             |
  4. +--------------------------------------------------------+
  5. | Project                                                |
  6. |   ->  Project                                          |
  7. |         ->  Table Scan on db1.t1                       |
  8. |               Filter Cond: (CAST(t1.id AS BIGINT) > 4) |
  9. +--------------------------------------------------------+
  10. 4 rows in set (0.03 sec)