You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
353 lines
12 KiB
353 lines
12 KiB
# Verifying the CTE-specific output of EXPLAIN
|
|
create table t1(a int);
|
|
insert into t1 values(1),(2);
|
|
# In JSON "materialized_from_subquery" for the 2 last references
|
|
# points to 1st reference: no duplication. In TRADITIONAL,
|
|
# The 2 last references are 1) not expanded (underlying tables
|
|
# are not shown) 2) shown as <derivedN> where N is ID of 1st
|
|
# reference. So users understand we have single materialization.
|
|
explain format=json with qn(a) as (select 1 from t1 limit 2)
|
|
select * from qn where qn.a=(select * from qn qn1 limit 1) ;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "0.35"
|
|
},
|
|
"table": {
|
|
"table_name": "qn",
|
|
"access_type": "ref",
|
|
"possible_keys": [
|
|
"<auto_key0>"
|
|
],
|
|
"key": "<auto_key0>",
|
|
"used_key_parts": [
|
|
"a"
|
|
],
|
|
"key_length": "4",
|
|
"ref": [
|
|
"const"
|
|
],
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "0.25",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "0.35",
|
|
"data_read_per_join": "16"
|
|
},
|
|
"used_columns": [
|
|
"a"
|
|
],
|
|
"attached_condition": "(`qn`.`a` = (/* select#3 */ select `qn1`.`a` from `qn` `qn1` limit 1))",
|
|
"attached_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
},
|
|
"table": {
|
|
"table_name": "qn1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 2,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.52",
|
|
"eval_cost": "0.20",
|
|
"prefix_cost": "2.73",
|
|
"data_read_per_join": "32"
|
|
},
|
|
"used_columns": [
|
|
"a"
|
|
],
|
|
"materialized_from_subquery": {
|
|
"sharing_temporary_table_with": {
|
|
"select_id": 2
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
],
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "0.45"
|
|
},
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 2,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "0.25",
|
|
"eval_cost": "0.20",
|
|
"prefix_cost": "0.45",
|
|
"data_read_per_join": "16"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 with `qn` (`a`) as (/* select#2 */ select 1 AS `1` from `test`.`t1` limit 2) /* select#1 */ select `qn`.`a` AS `a` from `qn` where (`qn`.`a` = (/* select#3 */ select `qn1`.`a` from `qn` `qn1` limit 1))
|
|
explain format=traditional with qn(a) as (select 1 from t1 limit 2)
|
|
select * from qn where qn.a=(select * from qn qn1 limit 1) ;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived2> NULL ref <auto_key0> <auto_key0> 4 const 1 100.00 Using where; Using index
|
|
3 SUBQUERY <derived2> NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
2 DERIVED t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
Warnings:
|
|
Note 1003 with `qn` (`a`) as (/* select#2 */ select 1 AS `1` from `test`.`t1` limit 2) /* select#1 */ select `qn`.`a` AS `a` from `qn` where (`qn`.`a` = (/* select#3 */ select `qn1`.`a` from `qn` `qn1` limit 1))
|
|
explain format=json with qn as (select cast("x" as char(100)) as a from t1 limit 2)
|
|
select (select * from qn) from qn, qn qn1;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "5.66"
|
|
},
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "qn",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 2,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.52",
|
|
"eval_cost": "0.20",
|
|
"prefix_cost": "2.73",
|
|
"data_read_per_join": "816"
|
|
},
|
|
"used_columns": [
|
|
"a"
|
|
],
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 4,
|
|
"cost_info": {
|
|
"query_cost": "0.45"
|
|
},
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 2,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "0.25",
|
|
"eval_cost": "0.20",
|
|
"prefix_cost": "0.45",
|
|
"data_read_per_join": "16"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "qn1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 4,
|
|
"filtered": "100.00",
|
|
"using_join_buffer": "Block Nested Loop",
|
|
"cost_info": {
|
|
"read_cost": "2.53",
|
|
"eval_cost": "0.40",
|
|
"prefix_cost": "5.66",
|
|
"data_read_per_join": "1K"
|
|
},
|
|
"used_columns": [
|
|
"a"
|
|
],
|
|
"materialized_from_subquery": {
|
|
"sharing_temporary_table_with": {
|
|
"select_id": 4
|
|
}
|
|
}
|
|
}
|
|
}
|
|
],
|
|
"select_list_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
},
|
|
"table": {
|
|
"table_name": "qn",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 2,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.52",
|
|
"eval_cost": "0.20",
|
|
"prefix_cost": "2.73",
|
|
"data_read_per_join": "816"
|
|
},
|
|
"used_columns": [
|
|
"a"
|
|
],
|
|
"materialized_from_subquery": {
|
|
"sharing_temporary_table_with": {
|
|
"select_id": 4
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 with `qn` as (/* select#4 */ select cast('x' as char(100) charset utf8mb4) AS `a` from `test`.`t1` limit 2) /* select#1 */ select (/* select#2 */ select `qn`.`a` from `qn`) AS `(select * from qn)` from `qn` join `qn` `qn1`
|
|
explain format=traditional with qn as (select cast("x" as char(100)) as a from t1 limit 2)
|
|
select (select * from qn) from qn, qn qn1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived4> NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
1 PRIMARY <derived4> NULL ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop)
|
|
4 DERIVED t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
2 SUBQUERY <derived4> NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
Warnings:
|
|
Note 1003 with `qn` as (/* select#4 */ select cast('x' as char(100) charset utf8mb4) AS `a` from `test`.`t1` limit 2) /* select#1 */ select (/* select#2 */ select `qn`.`a` from `qn`) AS `(select * from qn)` from `qn` join `qn` `qn1`
|
|
# Recursive query block has a mark:
|
|
# "recursive":true in JSON, "Recursive" on its first table in
|
|
# TRADITIONAL.
|
|
explain format=json with recursive qn as (select cast("x" as char(100)) as a from dual
|
|
union all
|
|
select concat("x",qn.a) from qn,t1 where
|
|
length(qn.a)<10)
|
|
select * from qn;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "3.06"
|
|
},
|
|
"table": {
|
|
"table_name": "qn",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 5,
|
|
"rows_produced_per_join": 5,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.56",
|
|
"eval_cost": "0.50",
|
|
"prefix_cost": "3.06",
|
|
"data_read_per_join": "1K"
|
|
},
|
|
"used_columns": [
|
|
"a"
|
|
],
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"union_result": {
|
|
"using_temporary_table": false,
|
|
"query_specifications": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"message": "No tables used"
|
|
}
|
|
},
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"recursive": true,
|
|
"cost_info": {
|
|
"query_cost": "3.37"
|
|
},
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "qn",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 2,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.52",
|
|
"eval_cost": "0.20",
|
|
"prefix_cost": "2.73",
|
|
"data_read_per_join": "816"
|
|
},
|
|
"used_columns": [
|
|
"a"
|
|
],
|
|
"attached_condition": "(length(`qn`.`a`) < 10)"
|
|
}
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 4,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "0.25",
|
|
"eval_cost": "0.40",
|
|
"prefix_cost": "3.38",
|
|
"data_read_per_join": "32"
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 with recursive `qn` as (/* select#2 */ select cast('x' as char(100) charset utf8mb4) AS `a` union all /* select#3 */ select concat('x',`qn`.`a`) AS `concat("x",qn.a)` from `qn` join `test`.`t1` where (length(`qn`.`a`) < 10)) /* select#1 */ select `qn`.`a` AS `a` from `qn`
|
|
explain format=traditional with recursive qn as (select cast("x" as char(100)) as a from dual
|
|
union all
|
|
select concat("x",qn.a) from qn,t1 where
|
|
length(qn.a)<10)
|
|
select * from qn;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 5 100.00 NULL
|
|
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION qn NULL ALL NULL NULL NULL NULL 2 100.00 Recursive; Using where
|
|
3 UNION t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
Warnings:
|
|
Note 1003 with recursive `qn` as (/* select#2 */ select cast('x' as char(100) charset utf8mb4) AS `a` union all /* select#3 */ select concat('x',`qn`.`a`) AS `concat("x",qn.a)` from `qn` join `test`.`t1` where (length(`qn`.`a`) < 10)) /* select#1 */ select `qn`.`a` AS `a` from `qn`
|
|
drop table t1;
|
|
|