2995 lines
105 KiB
Plaintext
2995 lines
105 KiB
Plaintext
set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on';
|
|
set @save_storage_engine= @@default_storage_engine;
|
|
set default_storage_engine=XENGINE;
|
|
set end_markers_in_json=on;
|
|
# new "FORMAT" keyword doesn't conflict with the FORMAT() function name:
|
|
SELECT FORMAT(1, 2), FORMAT(1, 2, 3);
|
|
FORMAT(1, 2) FORMAT(1, 2, 3)
|
|
1.00 1.00
|
|
Warnings:
|
|
Warning 1649 Unknown locale: '3'
|
|
# new "FORMAT" keyword is a valid identifier:
|
|
SET @FORMAT=10;
|
|
SELECT @FORMAT;
|
|
@FORMAT
|
|
10
|
|
CREATE TABLE t1 (format INT);
|
|
SELECT format FROM t1;
|
|
format
|
|
DROP TABLE t1;
|
|
# different ways of format name writing:
|
|
EXPLAIN FORMAT=traditional SELECT 1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1`
|
|
EXPLAIN FORMAT='TrAdItIoNaL' SELECT 1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1`
|
|
EXPLAIN FORMAT=JSON SELECT 1;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"message": "No tables used"
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1`
|
|
EXPLAIN FORMAT=foo SELECT 1;
|
|
ERROR HY000: Unknown EXPLAIN format name: 'foo'
|
|
# various EXPLAIN output
|
|
CREATE TABLE t1 (i INT);
|
|
CREATE TABLE t2 (i INT);
|
|
CREATE TABLE t3 (i INT);
|
|
CREATE TABLE t4 (i INT);
|
|
# no end markers in JSON:
|
|
set end_markers_in_json=off;
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
},
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
},
|
|
"used_columns": [
|
|
"i"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1`
|
|
set end_markers_in_json=on;
|
|
EXPLAIN INSERT INTO t1 VALUES (10);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 INSERT t1 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
|
Warnings:
|
|
Note 1003 insert into `test`.`t1` values (10)
|
|
EXPLAIN FORMAT=JSON INSERT INTO t1 VALUES (10);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"insert": true,
|
|
"table_name": "t1",
|
|
"access_type": "ALL"
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 insert into `test`.`t1` values (10)
|
|
EXPLAIN SELECT * FROM t1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1`
|
|
PREPARE stmt FROM 'EXPLAIN FORMAT=JSON SELECT * FROM t1';
|
|
EXECUTE stmt;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1`
|
|
EXECUTE stmt;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1`
|
|
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7);
|
|
INSERT INTO t2 VALUES (1), (2);
|
|
ANALYZE TABLE t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
# Check materialized derived table
|
|
set @optimizer_switch_saved= @@optimizer_switch;
|
|
set optimizer_switch='derived_merge=off';
|
|
EXPLAIN
|
|
SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT a1.i FROM (SELECT * FROM t1) a1, t2) a2) a3) a4;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 14 100.00 NULL
|
|
2 DERIVED <derived3> NULL ALL NULL NULL NULL NULL 14 100.00 NULL
|
|
3 DERIVED <derived4> NULL ALL NULL NULL NULL NULL 14 100.00 NULL
|
|
4 DERIVED t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
4 DERIVED <derived5> NULL ALL NULL NULL NULL NULL 7 100.00 Using join buffer (Block Nested Loop)
|
|
5 DERIVED t1 NULL ALL NULL NULL NULL NULL 7 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `a4`.`i` AS `i` from (/* select#2 */ select `a3`.`i` AS `i` from (/* select#3 */ select `a2`.`i` AS `i` from (/* select#4 */ select `a1`.`i` AS `i` from (/* select#5 */ select `test`.`t1`.`i` AS `i` from `test`.`t1`) `a1` join `test`.`t2`) `a2`) `a3`) `a4`
|
|
EXPLAIN FORMAT=JSON
|
|
SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT a1.i FROM (SELECT * FROM t1) a1, t2) a2) a3) a4;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "4.07"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "a4",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 14,
|
|
"rows_produced_per_join": 14,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.67",
|
|
"eval_cost": "1.40",
|
|
"prefix_cost": "4.08",
|
|
"data_read_per_join": "224"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "4.07"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "a3",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 14,
|
|
"rows_produced_per_join": 14,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.67",
|
|
"eval_cost": "1.40",
|
|
"prefix_cost": "4.08",
|
|
"data_read_per_join": "224"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost_info": {
|
|
"query_cost": "4.07"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "a2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 14,
|
|
"rows_produced_per_join": 14,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.67",
|
|
"eval_cost": "1.40",
|
|
"prefix_cost": "4.08",
|
|
"data_read_per_join": "224"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 4,
|
|
"cost_info": {
|
|
"query_cost": "6.71"
|
|
} /* cost_info */,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"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": "16"
|
|
} /* cost_info */
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "a1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 7,
|
|
"rows_produced_per_join": 14,
|
|
"filtered": "100.00",
|
|
"using_join_buffer": "Block Nested Loop",
|
|
"cost_info": {
|
|
"read_cost": "2.59",
|
|
"eval_cost": "1.40",
|
|
"prefix_cost": "6.71",
|
|
"data_read_per_join": "224"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 5,
|
|
"cost_info": {
|
|
"query_cost": "3.29"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 7,
|
|
"rows_produced_per_join": 7,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.59",
|
|
"eval_cost": "0.70",
|
|
"prefix_cost": "3.29",
|
|
"data_read_per_join": "56"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
} /* materialized_from_subquery */
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* query_block */
|
|
} /* materialized_from_subquery */
|
|
} /* table */
|
|
} /* query_block */
|
|
} /* materialized_from_subquery */
|
|
} /* table */
|
|
} /* query_block */
|
|
} /* materialized_from_subquery */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `a4`.`i` AS `i` from (/* select#2 */ select `a3`.`i` AS `i` from (/* select#3 */ select `a2`.`i` AS `i` from (/* select#4 */ select `a1`.`i` AS `i` from (/* select#5 */ select `test`.`t1`.`i` AS `i` from `test`.`t1`) `a1` join `test`.`t2`) `a2`) `a3`) `a4`
|
|
set optimizer_switch= @optimizer_switch_saved;
|
|
# subquery in WHERE
|
|
EXPLAIN SELECT * FROM t1 WHERE i IN (SELECT i FROM t2 WHERE t1.i = 10 ORDER BY RAND());
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 7 14.29 Using where
|
|
1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`i` = 10) and (`test`.`t2`.`i` = 10))
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE i IN (SELECT i FROM t2 WHERE t1.i = 10 ORDER BY RAND());
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "6.01"
|
|
} /* cost_info */,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 7,
|
|
"rows_produced_per_join": 0,
|
|
"filtered": "14.29",
|
|
"cost_info": {
|
|
"read_cost": "3.19",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "3.29",
|
|
"data_read_per_join": "7"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t1`.`i` = 10)"
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 0,
|
|
"filtered": "50.00",
|
|
"first_match": "t1",
|
|
"using_join_buffer": "Block Nested Loop",
|
|
"cost_info": {
|
|
"read_cost": "2.63",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "6.01",
|
|
"data_read_per_join": "7"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t2`.`i` = 10)"
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`i` = 10) and (`test`.`t2`.`i` = 10))
|
|
# two subqueries in WHERE
|
|
EXPLAIN SELECT * FROM t1
|
|
WHERE i IN (SELECT i FROM t2 WHERE t1.i = 10 ORDER BY RAND())
|
|
OR i IN (SELECT i FROM t4 ORDER BY RAND());
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using where
|
|
3 DEPENDENT SUBQUERY t4 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` where (<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`i` = 10) and (<cache>(`test`.`t1`.`i`) = `test`.`t2`.`i`)))) or <in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#3 */ select 1 from `test`.`t4` where (<cache>(`test`.`t1`.`i`) = `test`.`t4`.`i`))))
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1
|
|
WHERE i IN (SELECT i FROM t2 WHERE t1.i = 10 ORDER BY RAND())
|
|
OR i IN (SELECT i FROM t4 ORDER BY RAND());
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "3.29"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 7,
|
|
"rows_produced_per_join": 7,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.59",
|
|
"eval_cost": "0.70",
|
|
"prefix_cost": "3.29",
|
|
"data_read_per_join": "56"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */,
|
|
"attached_condition": "(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`i` = 10) and (<cache>(`test`.`t1`.`i`) = `test`.`t2`.`i`)))) or <in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#3 */ select 1 from `test`.`t4` where (<cache>(`test`.`t1`.`i`) = `test`.`t4`.`i`))))",
|
|
"attached_subqueries": [
|
|
{
|
|
"dependent": true,
|
|
"cacheable": false,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t4",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */,
|
|
"attached_condition": "(<cache>(`test`.`t1`.`i`) = `test`.`t4`.`i`)"
|
|
} /* table */
|
|
} /* query_block */
|
|
},
|
|
{
|
|
"dependent": true,
|
|
"cacheable": false,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "50.00",
|
|
"cost_info": {
|
|
"read_cost": "2.52",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.73",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */,
|
|
"attached_condition": "((`test`.`t1`.`i` = 10) and (<cache>(`test`.`t1`.`i`) = `test`.`t2`.`i`))"
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* attached_subqueries */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` where (<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`i` = 10) and (<cache>(`test`.`t1`.`i`) = `test`.`t2`.`i`)))) or <in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#3 */ select 1 from `test`.`t4` where (<cache>(`test`.`t1`.`i`) = `test`.`t4`.`i`))))
|
|
# simple UNION
|
|
EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t3;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 NULL
|
|
2 UNION t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
3 UNION t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
NULL UNION RESULT <union1,2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`i` AS `i` from `test`.`t2` union /* select#3 */ select `test`.`t3`.`i` AS `i` from `test`.`t3`
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t3;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"union_result": {
|
|
"using_temporary_table": true,
|
|
"table_name": "<union1,2,3>",
|
|
"access_type": "ALL",
|
|
"query_specifications": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "3.29"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 7,
|
|
"rows_produced_per_join": 7,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.59",
|
|
"eval_cost": "0.70",
|
|
"prefix_cost": "3.29",
|
|
"data_read_per_join": "56"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
},
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"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": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
},
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* query_specifications */
|
|
} /* union_result */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`i` AS `i` from `test`.`t2` union /* select#3 */ select `test`.`t3`.`i` AS `i` from `test`.`t3`
|
|
# more complex UNION
|
|
EXPLAIN (SELECT t1.i FROM t1 JOIN t2) UNION ALL (SELECT * FROM t3 WHERE i IN (SELECT i FROM t4 ORDER BY RAND()));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using join buffer (Block Nested Loop)
|
|
2 UNION t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
2 UNION t4 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; FirstMatch(t3); Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` join `test`.`t2` union all /* select#2 */ select `test`.`t3`.`i` AS `i` from `test`.`t3` semi join (`test`.`t4`) where (`test`.`t4`.`i` = `test`.`t3`.`i`)
|
|
EXPLAIN FORMAT=JSON (SELECT t1.i FROM t1 JOIN t2) UNION ALL (SELECT * FROM t3 WHERE i IN (SELECT i FROM t4 ORDER BY RAND()));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"union_result": {
|
|
"using_temporary_table": false,
|
|
"query_specifications": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "6.71"
|
|
} /* cost_info */,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"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": "16"
|
|
} /* cost_info */
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 7,
|
|
"rows_produced_per_join": 14,
|
|
"filtered": "100.00",
|
|
"using_join_buffer": "Block Nested Loop",
|
|
"cost_info": {
|
|
"read_cost": "2.59",
|
|
"eval_cost": "1.40",
|
|
"prefix_cost": "6.71",
|
|
"data_read_per_join": "112"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* query_block */
|
|
},
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "5.22"
|
|
} /* cost_info */,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t4",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"first_match": "t3",
|
|
"using_join_buffer": "Block Nested Loop",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "5.23",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t4`.`i` = `test`.`t3`.`i`)"
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* query_block */
|
|
}
|
|
] /* query_specifications */
|
|
} /* union_result */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` join `test`.`t2` union all /* select#2 */ select `test`.`t3`.`i` AS `i` from `test`.`t3` semi join (`test`.`t4`) where (`test`.`t4`.`i` = `test`.`t3`.`i`)
|
|
# UNION with subquery in outer ORDER BY
|
|
EXPLAIN (SELECT * FROM t1) UNION (SELECT * FROM t2) ORDER BY (SELECT i LIMIT 1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 NULL
|
|
2 UNION t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary; Using filesort
|
|
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1276 Field or reference 'i' of SELECT #3 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`i` AS `i` from `test`.`t2` order by (/* select#3 */ select `<union temporary>`.`i` limit 1)
|
|
EXPLAIN FORMAT=JSON (SELECT * FROM t1) UNION (SELECT * FROM t2) ORDER BY (SELECT i LIMIT 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"ordering_operation": {
|
|
"using_filesort": true,
|
|
"union_result": {
|
|
"using_temporary_table": true,
|
|
"table_name": "<union1,2>",
|
|
"access_type": "ALL",
|
|
"query_specifications": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "3.29"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 7,
|
|
"rows_produced_per_join": 7,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.59",
|
|
"eval_cost": "0.70",
|
|
"prefix_cost": "3.29",
|
|
"data_read_per_join": "56"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
},
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"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": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* query_specifications */
|
|
} /* union_result */,
|
|
"order_by_subqueries": [
|
|
{
|
|
"dependent": true,
|
|
"cacheable": false,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"message": "No tables used"
|
|
} /* query_block */
|
|
}
|
|
] /* order_by_subqueries */
|
|
} /* ordering_operation */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1276 Field or reference 'i' of SELECT #3 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`i` AS `i` from `test`.`t2` order by (/* select#3 */ select `<union temporary>`.`i` limit 1)
|
|
# optimizer-time subquery
|
|
EXPLAIN SELECT * FROM t1 ORDER BY (SELECT LENGTH(1) FROM t2 LIMIT 1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 NULL
|
|
2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` order by (/* select#2 */ select length(1) from `test`.`t2` limit 1)
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 ORDER BY (SELECT LENGTH(1) FROM t2 LIMIT 1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "3.29"
|
|
} /* cost_info */,
|
|
"ordering_operation": {
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 7,
|
|
"rows_produced_per_join": 7,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.59",
|
|
"eval_cost": "0.70",
|
|
"prefix_cost": "3.29",
|
|
"data_read_per_join": "56"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */,
|
|
"optimized_away_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"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": "16"
|
|
} /* cost_info */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* optimized_away_subqueries */
|
|
} /* ordering_operation */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` order by (/* select#2 */ select length(1) from `test`.`t2` limit 1)
|
|
# subquery in the HAVING clause
|
|
EXPLAIN SELECT * FROM t1 HAVING i > ALL (SELECT i FROM t2) OR i < ALL (SELECT i FROM t2);;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 NULL
|
|
3 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` having (<not>((`test`.`t1`.`i` <= <max>(/* select#2 */ select `test`.`t2`.`i` from `test`.`t2`))) or <not>((`test`.`t1`.`i` >= <min>(/* select#3 */ select `test`.`t2`.`i` from `test`.`t2`))))
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 HAVING i > ALL (SELECT i FROM t2) OR i < ALL (SELECT i FROM t2);;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "3.29"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 7,
|
|
"rows_produced_per_join": 7,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.59",
|
|
"eval_cost": "0.70",
|
|
"prefix_cost": "3.29",
|
|
"data_read_per_join": "56"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */,
|
|
"having_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"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": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
},
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"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": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* having_subqueries */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` having (<not>((`test`.`t1`.`i` <= <max>(/* select#2 */ select `test`.`t2`.`i` from `test`.`t2`))) or <not>((`test`.`t1`.`i` >= <min>(/* select#3 */ select `test`.`t2`.`i` from `test`.`t2`))))
|
|
# subquery in the GROUP BY clause
|
|
EXPLAIN SELECT * FROM t1 GROUP BY i > ALL (SELECT i FROM t2) OR i < ALL (SELECT i FROM t2);;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using temporary
|
|
3 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` group by (<not>(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`i`) <= `test`.`t2`.`i`) or (`test`.`t2`.`i` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`i`), true)))) or <not>(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#3 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`i`) >= `test`.`t2`.`i`) or (`test`.`t2`.`i` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`i`), true)))))
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 GROUP BY i > ALL (SELECT i FROM t2) OR i < ALL (SELECT i FROM t2);;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "3.29"
|
|
} /* cost_info */,
|
|
"grouping_operation": {
|
|
"using_temporary_table": true,
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 7,
|
|
"rows_produced_per_join": 7,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.59",
|
|
"eval_cost": "0.70",
|
|
"prefix_cost": "3.29",
|
|
"data_read_per_join": "56"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */,
|
|
"group_by_subqueries": [
|
|
{
|
|
"dependent": true,
|
|
"cacheable": false,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"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": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */,
|
|
"attached_condition": "<if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`i`) >= `test`.`t2`.`i`) or (`test`.`t2`.`i` is null)), true)"
|
|
} /* table */
|
|
} /* query_block */
|
|
},
|
|
{
|
|
"dependent": true,
|
|
"cacheable": false,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"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": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */,
|
|
"attached_condition": "<if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`i`) <= `test`.`t2`.`i`) or (`test`.`t2`.`i` is null)), true)"
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* group_by_subqueries */
|
|
} /* grouping_operation */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` group by (<not>(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`i`) <= `test`.`t2`.`i`) or (`test`.`t2`.`i` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`i`), true)))) or <not>(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#3 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`i`) >= `test`.`t2`.`i`) or (`test`.`t2`.`i` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`i`), true)))))
|
|
# subquery in the SELECT list
|
|
EXPLAIN SELECT (SELECT i + 1 FROM t1 ORDER BY RAND() LIMIT 1), i FROM t1;;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 NULL
|
|
2 UNCACHEABLE SUBQUERY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select (/* select#2 */ select (`test`.`t1`.`i` + 1) from `test`.`t1` order by rand() limit 1) AS `(SELECT i + 1 FROM t1 ORDER BY RAND() LIMIT 1)`,`test`.`t1`.`i` AS `i` from `test`.`t1`
|
|
EXPLAIN FORMAT=JSON SELECT (SELECT i + 1 FROM t1 ORDER BY RAND() LIMIT 1), i FROM t1;;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "3.29"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 7,
|
|
"rows_produced_per_join": 7,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.59",
|
|
"eval_cost": "0.70",
|
|
"prefix_cost": "3.29",
|
|
"data_read_per_join": "56"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */,
|
|
"select_list_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": false,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "3.29"
|
|
} /* cost_info */,
|
|
"ordering_operation": {
|
|
"using_temporary_table": true,
|
|
"using_filesort": true,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 7,
|
|
"rows_produced_per_join": 7,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.59",
|
|
"eval_cost": "0.70",
|
|
"prefix_cost": "3.29",
|
|
"data_read_per_join": "56"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* ordering_operation */
|
|
} /* query_block */
|
|
}
|
|
] /* select_list_subqueries */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select (/* select#2 */ select (`test`.`t1`.`i` + 1) from `test`.`t1` order by rand() limit 1) AS `(SELECT i + 1 FROM t1 ORDER BY RAND() LIMIT 1)`,`test`.`t1`.`i` AS `i` from `test`.`t1`
|
|
DROP TABLE t1, t2, t3, t4;
|
|
# derived table that is optimized out
|
|
CREATE TABLE t1 (i INT);
|
|
EXPLAIN SELECT 1 FROM (SELECT 1 AS x FROM t1) tt WHERE x;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where true
|
|
EXPLAIN FORMAT= JSON SELECT 1 FROM (SELECT 1 AS x FROM t1) tt WHERE x;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where true
|
|
DROP TABLE t1;
|
|
# complex subqueries
|
|
CREATE TABLE t1 (a INT, b INT);
|
|
CREATE TABLE t2 (c INT, d INT);
|
|
CREATE TABLE t3 (e INT);
|
|
CREATE TABLE t4 (f INT, g INT);
|
|
INSERT INTO t1 VALUES (1,10), (2,10);
|
|
INSERT INTO t2 VALUES (2,10), (2,20);
|
|
INSERT INTO t3 VALUES (10), (30);
|
|
INSERT INTO t4 VALUES (2,10), (2,10);
|
|
ANALYZE TABLE t1, t2, t3, t4;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status OK
|
|
test.t4 analyze status OK
|
|
EXPLAIN SELECT * FROM t1 WHERE t1.a IN (SELECT c FROM t2 WHERE (SELECT e FROM t3) < SOME(SELECT e FROM t3 WHERE t1.b));;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
|
|
4 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
3 SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.b' of SELECT #4 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where ((0 <> `test`.`t1`.`b`) and <if>(outer_field_is_not_null, ((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or (`test`.`t3`.`e` is null)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))))
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE t1.a IN (SELECT c FROM t2 WHERE (SELECT e FROM t3) < SOME(SELECT e FROM t3 WHERE t1.b));;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "5.65"
|
|
} /* cost_info */,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"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"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
] /* used_columns */,
|
|
"attached_condition": "<nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where ((0 <> `test`.`t1`.`b`) and <if>(outer_field_is_not_null, ((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or (`test`.`t3`.`e` is null)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true))))",
|
|
"attached_subqueries": [
|
|
{
|
|
"dependent": true,
|
|
"cacheable": false,
|
|
"query_block": {
|
|
"select_id": 4,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t3",
|
|
"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": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"e"
|
|
] /* used_columns */,
|
|
"attached_condition": "((0 <> `test`.`t1`.`b`) and <if>(outer_field_is_not_null, ((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or (`test`.`t3`.`e` is null)), true))"
|
|
} /* table */
|
|
} /* query_block */
|
|
},
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t3",
|
|
"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": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"e"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* attached_subqueries */
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 2,
|
|
"filtered": "50.00",
|
|
"first_match": "t1",
|
|
"using_join_buffer": "Block Nested Loop",
|
|
"cost_info": {
|
|
"read_cost": "2.53",
|
|
"eval_cost": "0.20",
|
|
"prefix_cost": "5.65",
|
|
"data_read_per_join": "32"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"c"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t2`.`c` = `test`.`t1`.`a`)"
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.b' of SELECT #4 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where ((0 <> `test`.`t1`.`b`) and <if>(outer_field_is_not_null, ((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or (`test`.`t3`.`e` is null)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))))
|
|
DROP TABLE t1, t2, t3, t4;
|
|
# semi-join materialization (if enabled)
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1);
|
|
CREATE TABLE t2 (a INT) SELECT * FROM t1;
|
|
CREATE TABLE t3 (a INT) SELECT * FROM t1;
|
|
CREATE TABLE t4 (a INT) SELECT * FROM t1;
|
|
ANALYZE TABLE t1, t2, t3, t4;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status OK
|
|
test.t4 analyze status OK
|
|
EXPLAIN FORMAT=JSON
|
|
SELECT * FROM t1
|
|
WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.a > 0) AND
|
|
t1.a IN (SELECT t3.a FROM t3 WHERE t3.a IN
|
|
(SELECT t4.a FROM t4 WHERE a > 0));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "24.28"
|
|
} /* cost_info */,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 12,
|
|
"rows_produced_per_join": 12,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.65",
|
|
"eval_cost": "1.20",
|
|
"prefix_cost": "3.85",
|
|
"data_read_per_join": "96"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t1`.`a` is not null)"
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "<subquery3>",
|
|
"access_type": "eq_ref",
|
|
"key": "<auto_distinct_key>",
|
|
"key_length": "5",
|
|
"ref": [
|
|
"test.t1.a"
|
|
] /* ref */,
|
|
"rows_examined_per_scan": 1,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"query_block": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t4",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 12,
|
|
"rows_produced_per_join": 3,
|
|
"filtered": "33.33",
|
|
"cost_info": {
|
|
"read_cost": "3.45",
|
|
"eval_cost": "0.40",
|
|
"prefix_cost": "3.85",
|
|
"data_read_per_join": "31"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t4`.`a` > 0)"
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 12,
|
|
"rows_produced_per_join": 4,
|
|
"filtered": "10.00",
|
|
"using_join_buffer": "Block Nested Loop",
|
|
"cost_info": {
|
|
"read_cost": "2.65",
|
|
"eval_cost": "0.48",
|
|
"prefix_cost": "11.30",
|
|
"data_read_per_join": "38"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t3`.`a` = `test`.`t4`.`a`)"
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* query_block */
|
|
} /* materialized_from_subquery */
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "<subquery2>",
|
|
"access_type": "eq_ref",
|
|
"key": "<auto_distinct_key>",
|
|
"key_length": "5",
|
|
"ref": [
|
|
"test.t1.a"
|
|
] /* ref */,
|
|
"rows_examined_per_scan": 1,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"query_block": {
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 12,
|
|
"rows_produced_per_join": 3,
|
|
"filtered": "33.33",
|
|
"cost_info": {
|
|
"read_cost": "3.45",
|
|
"eval_cost": "0.40",
|
|
"prefix_cost": "3.85",
|
|
"data_read_per_join": "31"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t2`.`a` > 0)"
|
|
} /* table */
|
|
} /* query_block */
|
|
} /* materialized_from_subquery */
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3` join `test`.`t4`) semi join (`test`.`t2`) where ((`<subquery3>`.`a` = `test`.`t1`.`a`) and (`<subquery2>`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t4`.`a`) and (`test`.`t4`.`a` > 0) and (`test`.`t2`.`a` > 0))
|
|
DROP TABLE t1, t2, t3, t4;
|
|
# the same subquery is associated with two different JOIN_TABs
|
|
CREATE TABLE t1 (
|
|
i1 INTEGER NOT NULL,
|
|
c1 VARCHAR(1) NOT NULL
|
|
) charset latin1 collate latin1_bin;
|
|
INSERT INTO t1 VALUES (2,'w');
|
|
CREATE TABLE t2 (
|
|
i1 INTEGER NOT NULL,
|
|
c1 VARCHAR(1) NOT NULL,
|
|
c2 VARCHAR(1) NOT NULL,
|
|
KEY (c1, i1)
|
|
) charset latin1 collate latin1_bin;
|
|
INSERT INTO t2 VALUES (8,'d','d');
|
|
INSERT INTO t2 VALUES (4,'v','v');
|
|
CREATE TABLE t3 (
|
|
c1 VARCHAR(1) NOT NULL
|
|
) charset latin1 collate latin1_bin;
|
|
INSERT INTO t3 VALUES ('v');
|
|
ANALYZE TABLE t1, t2, t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status OK
|
|
EXPLAIN FORMAT=json
|
|
SELECT i1
|
|
FROM t1
|
|
WHERE EXISTS (SELECT t2.c1
|
|
FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1))
|
|
WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
|
|
FROM t3));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "5.92"
|
|
} /* cost_info */,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i1",
|
|
"c1"
|
|
] /* used_columns */,
|
|
"attached_condition": "((/* select#3 */ select min(`test`.`t3`.`c1`) from `test`.`t3`) <> `test`.`t1`.`c1`)",
|
|
"attached_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"c1"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* attached_subqueries */
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "5.23",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"c1"
|
|
] /* used_columns */
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ref",
|
|
"possible_keys": [
|
|
"c1"
|
|
] /* possible_keys */,
|
|
"key": "c1",
|
|
"used_key_parts": [
|
|
"c1"
|
|
] /* used_key_parts */,
|
|
"key_length": "3",
|
|
"ref": [
|
|
"test.t3.c1"
|
|
] /* ref */,
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "50.00",
|
|
"first_match": "t1",
|
|
"cost_info": {
|
|
"read_cost": "0.50",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "5.93",
|
|
"data_read_per_join": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"c1",
|
|
"c2"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t2`.`c2` = (/* select#3 */ select min(`test`.`t3`.`c1`) from `test`.`t3`))",
|
|
"attached_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"c1"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* attached_subqueries */
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.c1' of SELECT #2 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t2`.`c1` = `test`.`t3`.`c1`) and (`test`.`t2`.`c2` = (/* select#3 */ select min(`test`.`t3`.`c1`) from `test`.`t3`)) and ((/* select#3 */ select min(`test`.`t3`.`c1`) from `test`.`t3`) <> `test`.`t1`.`c1`))
|
|
DROP TABLE t1, t2, t3;
|
|
# multiple materialization groups
|
|
CREATE TABLE t1 (c_key INT, KEY c_key (c_key));
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
CREATE TABLE t2 (c INT, c_key INT);
|
|
INSERT INTO t2 VALUES (8,5),(4,5),(8,1);
|
|
CREATE TABLE t3 LIKE t1;
|
|
INSERT INTO t3 SELECT * FROM t1;
|
|
CREATE TABLE t4 LIKE t2;
|
|
INSERT INTO t4 SELECT * FROM t2;
|
|
CREATE TABLE t5 (c INT);
|
|
INSERT INTO t5 VALUES (1), (2), (3);
|
|
ANALYZE TABLE t1, t2, t3, t4, t5;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status OK
|
|
test.t4 analyze status OK
|
|
test.t5 analyze status OK
|
|
# This should show two materialization groups where applicable
|
|
EXPLAIN SELECT * FROM t5
|
|
WHERE c IN (SELECT t2.c FROM t1 JOIN t2 ON t2.c_key = t1.c_key)
|
|
AND c IN (SELECT t4.c FROM t3 JOIN t4 ON t4.c_key = t3.c_key);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t5 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 SIMPLE <subquery2> NULL eq_ref <auto_distinct_key> <auto_distinct_key> 5 test.t5.c 1 100.00 NULL
|
|
1 SIMPLE <subquery3> NULL eq_ref <auto_distinct_key> <auto_distinct_key> 5 test.t5.c 1 100.00 NULL
|
|
2 MATERIALIZED t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 MATERIALIZED t1 NULL ref c_key c_key 5 test.t2.c_key 3 100.00 Using index
|
|
3 MATERIALIZED t4 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
3 MATERIALIZED t3 NULL ref c_key c_key 5 test.t4.c_key 3 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t5`.`c` AS `c` from `test`.`t5` semi join (`test`.`t1` join `test`.`t2`) semi join (`test`.`t3` join `test`.`t4`) where ((`test`.`t1`.`c_key` = `test`.`t2`.`c_key`) and (`test`.`t3`.`c_key` = `test`.`t4`.`c_key`) and (`<subquery2>`.`c` = `test`.`t5`.`c`) and (`<subquery3>`.`c` = `test`.`t5`.`c`))
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t5
|
|
WHERE c IN (SELECT t2.c FROM t1 JOIN t2 ON t2.c_key = t1.c_key)
|
|
AND c IN (SELECT t4.c FROM t3 JOIN t4 ON t4.c_key = t3.c_key);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "16.22"
|
|
} /* cost_info */,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t5",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 3,
|
|
"rows_produced_per_join": 3,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.54",
|
|
"eval_cost": "0.30",
|
|
"prefix_cost": "2.84",
|
|
"data_read_per_join": "24"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"c"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t5`.`c` is not null)"
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "<subquery2>",
|
|
"access_type": "eq_ref",
|
|
"key": "<auto_distinct_key>",
|
|
"key_length": "5",
|
|
"ref": [
|
|
"test.t5.c"
|
|
] /* ref */,
|
|
"rows_examined_per_scan": 1,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"query_block": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 3,
|
|
"rows_produced_per_join": 3,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.54",
|
|
"eval_cost": "0.30",
|
|
"prefix_cost": "2.84",
|
|
"data_read_per_join": "48"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"c",
|
|
"c_key"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t2`.`c_key` is not null)"
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ref",
|
|
"possible_keys": [
|
|
"c_key"
|
|
] /* possible_keys */,
|
|
"key": "c_key",
|
|
"used_key_parts": [
|
|
"c_key"
|
|
] /* used_key_parts */,
|
|
"key_length": "5",
|
|
"ref": [
|
|
"test.t2.c_key"
|
|
] /* ref */,
|
|
"rows_examined_per_scan": 3,
|
|
"rows_produced_per_join": 9,
|
|
"filtered": "100.00",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "0.75",
|
|
"eval_cost": "0.90",
|
|
"prefix_cost": "4.49",
|
|
"data_read_per_join": "72"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"c_key"
|
|
] /* used_columns */
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* query_block */
|
|
} /* materialized_from_subquery */
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "<subquery3>",
|
|
"access_type": "eq_ref",
|
|
"key": "<auto_distinct_key>",
|
|
"key_length": "5",
|
|
"ref": [
|
|
"test.t5.c"
|
|
] /* ref */,
|
|
"rows_examined_per_scan": 1,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"query_block": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t4",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 3,
|
|
"rows_produced_per_join": 3,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.54",
|
|
"eval_cost": "0.30",
|
|
"prefix_cost": "2.84",
|
|
"data_read_per_join": "48"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"c",
|
|
"c_key"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t4`.`c_key` is not null)"
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ref",
|
|
"possible_keys": [
|
|
"c_key"
|
|
] /* possible_keys */,
|
|
"key": "c_key",
|
|
"used_key_parts": [
|
|
"c_key"
|
|
] /* used_key_parts */,
|
|
"key_length": "5",
|
|
"ref": [
|
|
"test.t4.c_key"
|
|
] /* ref */,
|
|
"rows_examined_per_scan": 3,
|
|
"rows_produced_per_join": 9,
|
|
"filtered": "100.00",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "0.75",
|
|
"eval_cost": "0.90",
|
|
"prefix_cost": "4.49",
|
|
"data_read_per_join": "72"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"c_key"
|
|
] /* used_columns */
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* query_block */
|
|
} /* materialized_from_subquery */
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t5`.`c` AS `c` from `test`.`t5` semi join (`test`.`t1` join `test`.`t2`) semi join (`test`.`t3` join `test`.`t4`) where ((`test`.`t1`.`c_key` = `test`.`t2`.`c_key`) and (`test`.`t3`.`c_key` = `test`.`t4`.`c_key`) and (`<subquery2>`.`c` = `test`.`t5`.`c`) and (`<subquery3>`.`c` = `test`.`t5`.`c`))
|
|
DROP TABLE t1, t2, t3, t4, t5;
|
|
CREATE TABLE t1 (i INT);
|
|
CREATE TABLE t2 (i INT);
|
|
CREATE TABLE t3 (i INT);
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO t2 VALUES (1);
|
|
INSERT INTO t3 VALUES (1);
|
|
ANALYZE TABLE t1, t2, t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status OK
|
|
# Subqueries in UPDATE values list
|
|
EXPLAIN UPDATE t1 SET i=(SELECT i FROM t2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` set `test`.`t1`.`i` = (/* select#2 */ select `test`.`t2`.`i` from `test`.`t2`)
|
|
EXPLAIN FORMAT=JSON UPDATE t1 SET i=(SELECT i FROM t2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"update": true,
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"filtered": "100.00"
|
|
} /* table */,
|
|
"update_value_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* update_value_subqueries */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` set `test`.`t1`.`i` = (/* select#2 */ select `test`.`t2`.`i` from `test`.`t2`)
|
|
EXPLAIN UPDATE t1, t2 SET t1.i=(SELECT i FROM t3);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
2 SUBQUERY t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` join `test`.`t2` set `test`.`t1`.`i` = (/* select#2 */ select `test`.`t3`.`i` from `test`.`t3`)
|
|
EXPLAIN FORMAT=JSON UPDATE t1, t2 SET t1.i=(SELECT i FROM t3);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "5.22"
|
|
} /* cost_info */,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"update": true,
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "5.22",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */,
|
|
"update_value_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* update_value_subqueries */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` join `test`.`t2` set `test`.`t1`.`i` = (/* select#2 */ select `test`.`t3`.`i` from `test`.`t3`)
|
|
# INSERT ... ON DUPLICATE KEY UPDATE x=(SELECT ...) value list
|
|
EXPLAIN INSERT INTO t1 (i)
|
|
SELECT i FROM t2 ON DUPLICATE KEY UPDATE i=(SELECT i FROM t2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 INSERT t1 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 insert into `test`.`t1` (`test`.`t1`.`i`) /* select#1 */ select `test`.`t2`.`i` AS `i` from `test`.`t2` on duplicate key update `test`.`t1`.`i` = (/* select#2 */ select `test`.`t2`.`i` from `test`.`t2`)
|
|
EXPLAIN FORMAT=JSON INSERT INTO t1 (i)
|
|
SELECT i FROM t2 ON DUPLICATE KEY UPDATE i=(SELECT i FROM t2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"insert": true,
|
|
"table_name": "t1",
|
|
"access_type": "ALL"
|
|
} /* table */,
|
|
"insert_from": {
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* insert_from */,
|
|
"insert_update_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* insert_update_subqueries */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 insert into `test`.`t1` (`test`.`t1`.`i`) /* select#1 */ select `test`.`t2`.`i` AS `i` from `test`.`t2` on duplicate key update `test`.`t1`.`i` = (/* select#2 */ select `test`.`t2`.`i` from `test`.`t2`)
|
|
EXPLAIN INSERT INTO t1 VALUES (1)
|
|
ON DUPLICATE KEY UPDATE i = (SELECT i FROM t2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 INSERT t1 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
|
2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 insert into `test`.`t1` values (1) on duplicate key update `test`.`t1`.`i` = (/* select#2 */ select `test`.`t2`.`i` from `test`.`t2`)
|
|
EXPLAIN FORMAT=JSON INSERT INTO t1 VALUES (1)
|
|
ON DUPLICATE KEY UPDATE i = (SELECT i FROM t2);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"insert": true,
|
|
"table_name": "t1",
|
|
"access_type": "ALL"
|
|
} /* table */,
|
|
"insert_update_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* insert_update_subqueries */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 insert into `test`.`t1` values (1) on duplicate key update `test`.`t1`.`i` = (/* select#2 */ select `test`.`t2`.`i` from `test`.`t2`)
|
|
# Subqueries in INSERT VALUES tuples:
|
|
EXPLAIN INSERT INTO t3 VALUES((SELECT i FROM t1)), ((SELECT i FROM t2));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 INSERT t3 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
|
3 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
2 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 insert into `test`.`t3` values ((/* select#2 */ select `test`.`t1`.`i` from `test`.`t1`)),((/* select#3 */ select `test`.`t2`.`i` from `test`.`t2`))
|
|
EXPLAIN FORMAT=JSON INSERT INTO t3 VALUES((SELECT i FROM t1)), ((SELECT i FROM t2));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"insert": true,
|
|
"table_name": "t3",
|
|
"access_type": "ALL"
|
|
} /* table */,
|
|
"insert_values_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
},
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "2.61"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* insert_values_subqueries */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 insert into `test`.`t3` values ((/* select#2 */ select `test`.`t1`.`i` from `test`.`t1`)),((/* select#3 */ select `test`.`t2`.`i` from `test`.`t2`))
|
|
DROP TABLE t1, t2, t3;
|
|
# Various queries
|
|
EXPLAIN SELECT a, b FROM
|
|
(SELECT 1 AS a, 2 AS b
|
|
UNION ALL
|
|
SELECT 1 AS a, 2 AS b) t1
|
|
GROUP BY a
|
|
ORDER BY b DESC;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
|
|
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from (/* select#2 */ select 1 AS `a`,2 AS `b` union all /* select#3 */ select 1 AS `a`,2 AS `b`) `t1` group by `t1`.`a` order by `t1`.`b` desc
|
|
EXPLAIN FORMAT=JSON SELECT a, b FROM
|
|
(SELECT 1 AS a, 2 AS b
|
|
UNION ALL
|
|
SELECT 1 AS a, 2 AS b) t1
|
|
GROUP BY a
|
|
ORDER BY b DESC;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"ordering_operation": {
|
|
"using_filesort": true,
|
|
"grouping_operation": {
|
|
"using_temporary_table": true,
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"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": "48"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
] /* used_columns */,
|
|
"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"
|
|
} /* query_block */
|
|
},
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"message": "No tables used"
|
|
} /* query_block */
|
|
}
|
|
] /* query_specifications */
|
|
} /* union_result */
|
|
} /* query_block */
|
|
} /* materialized_from_subquery */
|
|
} /* table */
|
|
} /* grouping_operation */
|
|
} /* ordering_operation */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from (/* select#2 */ select 1 AS `a`,2 AS `b` union all /* select#3 */ select 1 AS `a`,2 AS `b`) `t1` group by `t1`.`a` order by `t1`.`b` desc
|
|
#
|
|
CREATE TABLE t1(a INT, b INT);
|
|
INSERT INTO t1 VALUES (), ();
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT 1 FROM t1 GROUP BY GREATEST(t1.a, (SELECT 1 FROM (SELECT t1.b FROM t1, t1 t2 ORDER BY t1.a, t1.a LIMIT 1) AS d));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary
|
|
2 SUBQUERY <derived3> NULL system NULL NULL NULL NULL 1 100.00 NULL
|
|
3 DERIVED t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
|
|
3 DERIVED t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` group by greatest(`test`.`t1`.`a`,(/* select#2 */ select 1 from dual))
|
|
EXPLAIN FORMAT=JSON SELECT 1 FROM t1 GROUP BY GREATEST(t1.a, (SELECT 1 FROM (SELECT t1.b FROM t1, t1 t2 ORDER BY t1.a, t1.a LIMIT 1) AS d));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"grouping_operation": {
|
|
"using_temporary_table": true,
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"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"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a"
|
|
] /* used_columns */
|
|
} /* table */,
|
|
"group_by_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "1.00"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "d",
|
|
"access_type": "system",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "0.00",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "0.00",
|
|
"data_read_per_join": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"b"
|
|
] /* used_columns */,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost_info": {
|
|
"query_cost": "5.65"
|
|
} /* cost_info */,
|
|
"ordering_operation": {
|
|
"using_temporary_table": true,
|
|
"using_filesort": true,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"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"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
] /* used_columns */
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"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.65",
|
|
"data_read_per_join": "64"
|
|
} /* cost_info */
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* ordering_operation */
|
|
} /* query_block */
|
|
} /* materialized_from_subquery */
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* group_by_subqueries */
|
|
} /* grouping_operation */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` group by greatest(`test`.`t1`.`a`,(/* select#2 */ select 1 from dual))
|
|
DROP TABLE t1;
|
|
#
|
|
CREATE TABLE t1(f1 INT);
|
|
INSERT INTO t1 VALUES (1),(1);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT 1 FROM t1 WHERE (SELECT (SELECT 1 FROM t1 GROUP BY f1));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
3 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary
|
|
Warnings:
|
|
Note 1249 Select 2 was reduced during optimization
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where true
|
|
EXPLAIN FORMAT=JSON SELECT 1 FROM t1 WHERE (SELECT (SELECT 1 FROM t1 GROUP BY f1));
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"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": "16"
|
|
} /* cost_info */
|
|
} /* table */,
|
|
"optimized_away_subqueries": [
|
|
{
|
|
"dependent": false,
|
|
"cacheable": true,
|
|
"query_block": {
|
|
"select_id": 3,
|
|
"cost_info": {
|
|
"query_cost": "2.72"
|
|
} /* cost_info */,
|
|
"grouping_operation": {
|
|
"using_temporary_table": true,
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"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": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"f1"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* grouping_operation */
|
|
} /* query_block */
|
|
}
|
|
] /* optimized_away_subqueries */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1249 Select 2 was reduced during optimization
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where true
|
|
DROP TABLE t1;
|
|
#
|
|
CREATE TABLE t1 (i INT);
|
|
CREATE TABLE t2 (i INT, j INT);
|
|
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
|
|
INSERT INTO t2 SELECT i, i * 10 FROM t1;
|
|
ANALYZE TABLE t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
EXPLAIN SELECT * FROM t1 ORDER BY (SELECT t2.j FROM t2 WHERE t2.i = t1.i);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 10 100.00 Using filesort
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 10 10.00 Using where
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` order by (/* select#2 */ select `test`.`t2`.`j` from `test`.`t2` where (`test`.`t2`.`i` = `test`.`t1`.`i`))
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 ORDER BY (SELECT t2.j FROM t2 WHERE t2.i = t1.i);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "3.62"
|
|
} /* cost_info */,
|
|
"ordering_operation": {
|
|
"using_filesort": true,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 10,
|
|
"rows_produced_per_join": 10,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.62",
|
|
"eval_cost": "1.00",
|
|
"prefix_cost": "3.62",
|
|
"data_read_per_join": "80"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */,
|
|
"order_by_subqueries": [
|
|
{
|
|
"dependent": true,
|
|
"cacheable": false,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "3.62"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 10,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "10.00",
|
|
"cost_info": {
|
|
"read_cost": "2.62",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "3.62",
|
|
"data_read_per_join": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i",
|
|
"j"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t2`.`i` = `test`.`t1`.`i`)"
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* order_by_subqueries */
|
|
} /* ordering_operation */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` order by (/* select#2 */ select `test`.`t2`.`j` from `test`.`t2` where (`test`.`t2`.`i` = `test`.`t1`.`i`))
|
|
EXPLAIN SELECT * FROM t1 GROUP BY (SELECT t2.j FROM t2 WHERE t2.i = t1.i);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 10 100.00 Using temporary
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 10 10.00 Using where
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` group by (/* select#2 */ select `test`.`t2`.`j` from `test`.`t2` where (`test`.`t2`.`i` = `test`.`t1`.`i`))
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 GROUP BY (SELECT t2.j FROM t2 WHERE t2.i = t1.i);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "3.62"
|
|
} /* cost_info */,
|
|
"grouping_operation": {
|
|
"using_temporary_table": true,
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 10,
|
|
"rows_produced_per_join": 10,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.62",
|
|
"eval_cost": "1.00",
|
|
"prefix_cost": "3.62",
|
|
"data_read_per_join": "80"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i"
|
|
] /* used_columns */
|
|
} /* table */,
|
|
"group_by_subqueries": [
|
|
{
|
|
"dependent": true,
|
|
"cacheable": false,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"cost_info": {
|
|
"query_cost": "3.62"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 10,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "10.00",
|
|
"cost_info": {
|
|
"read_cost": "2.62",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "3.62",
|
|
"data_read_per_join": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"i",
|
|
"j"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t2`.`i` = `test`.`t1`.`i`)"
|
|
} /* table */
|
|
} /* query_block */
|
|
}
|
|
] /* group_by_subqueries */
|
|
} /* grouping_operation */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` group by (/* select#2 */ select `test`.`t2`.`j` from `test`.`t2` where (`test`.`t2`.`i` = `test`.`t1`.`i`))
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k1 (a, b));
|
|
INSERT INTO t1 VALUES (10,1),(10,2),(10,3),(20,4),(20,5),(20,6),
|
|
(30,7),(30,8),(30,9),(40,10),(40,11),(40,12),(40,13),
|
|
(40,14),(40,15),(40,16),(40,17),(40,18),(40,19),(40,20);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN FORMAT=JSON SELECT a, MIN(b) AS b FROM t1 GROUP BY a ORDER BY b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "1.75"
|
|
} /* cost_info */,
|
|
"ordering_operation": {
|
|
"using_temporary_table": true,
|
|
"using_filesort": true,
|
|
"grouping_operation": {
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "range",
|
|
"possible_keys": [
|
|
"k1"
|
|
] /* possible_keys */,
|
|
"key": "k1",
|
|
"used_key_parts": [
|
|
"a"
|
|
] /* used_key_parts */,
|
|
"key_length": "4",
|
|
"rows_examined_per_scan": 6,
|
|
"rows_produced_per_join": 6,
|
|
"filtered": "100.00",
|
|
"using_index_for_group_by": true,
|
|
"cost_info": {
|
|
"read_cost": "1.15",
|
|
"eval_cost": "0.60",
|
|
"prefix_cost": "1.75",
|
|
"data_read_per_join": "96"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* grouping_operation */
|
|
} /* ordering_operation */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,min(`test`.`t1`.`b`) AS `b` from `test`.`t1` group by `test`.`t1`.`a` order by `b`
|
|
DROP TABLE t1;
|
|
#
|
|
CREATE TABLE t1 (a INT NOT NULL, b CHAR(3) NOT NULL, PRIMARY KEY (a)) charset utf8mb4;
|
|
INSERT INTO t1 VALUES (1,'ABC'), (2,'EFG'), (3,'HIJ');
|
|
CREATE TABLE t2 (a INT NOT NULL,b CHAR(3) NOT NULL,PRIMARY KEY (a, b)) charset utf8mb4;
|
|
INSERT INTO t2 VALUES (1,'a'),(1,'b'),(3,'F');
|
|
ANALYZE TABLE t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
EXPLAIN FORMAT=JSON SELECT t1.a, GROUP_CONCAT(t2.b) AS b FROM t1 LEFT JOIN t2 ON t1.a=t2.a GROUP BY t1.a ORDER BY t1.b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "4.49"
|
|
} /* cost_info */,
|
|
"ordering_operation": {
|
|
"using_temporary_table": true,
|
|
"using_filesort": true,
|
|
"grouping_operation": {
|
|
"using_filesort": false,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "index",
|
|
"possible_keys": [
|
|
"PRIMARY"
|
|
] /* possible_keys */,
|
|
"key": "PRIMARY",
|
|
"used_key_parts": [
|
|
"a"
|
|
] /* used_key_parts */,
|
|
"key_length": "4",
|
|
"rows_examined_per_scan": 3,
|
|
"rows_produced_per_join": 3,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.54",
|
|
"eval_cost": "0.30",
|
|
"prefix_cost": "2.84",
|
|
"data_read_per_join": "72"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
] /* used_columns */
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ref",
|
|
"possible_keys": [
|
|
"PRIMARY"
|
|
] /* possible_keys */,
|
|
"key": "PRIMARY",
|
|
"used_key_parts": [
|
|
"a"
|
|
] /* used_key_parts */,
|
|
"key_length": "4",
|
|
"ref": [
|
|
"test.t1.a"
|
|
] /* ref */,
|
|
"rows_examined_per_scan": 3,
|
|
"rows_produced_per_join": 9,
|
|
"filtered": "100.00",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "0.76",
|
|
"eval_cost": "0.90",
|
|
"prefix_cost": "4.49",
|
|
"data_read_per_join": "216"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
] /* used_columns */
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* grouping_operation */
|
|
} /* ordering_operation */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,group_concat(`test`.`t2`.`b` separator ',') AS `b` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where true group by `test`.`t1`.`a` order by `test`.`t1`.`b`
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
#
|
|
CREATE TABLE t1 (a INT, b INT);
|
|
INSERT INTO t1 VALUES
|
|
(1,4),
|
|
(2,2), (2,2),
|
|
(4,1), (4,1), (4,1), (4,1),
|
|
(2,1), (2,1);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN FORMAT=JSON SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "12.51"
|
|
} /* cost_info */,
|
|
"grouping_operation": {
|
|
"using_filesort": true,
|
|
"cost_info": {
|
|
"sort_cost": "9.00"
|
|
} /* cost_info */,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 9,
|
|
"rows_produced_per_join": 9,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.61",
|
|
"eval_cost": "0.90",
|
|
"prefix_cost": "3.51",
|
|
"data_read_per_join": "144"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* grouping_operation */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select sum(`test`.`t1`.`b`) AS `SUM(b)` from `test`.`t1` group by `test`.`t1`.`a` with rollup
|
|
DROP TABLE t1;
|
|
# Composition of DISTINCT, GROUP BY and ORDER BY
|
|
CREATE TABLE t1 (a INT, b INT);
|
|
INSERT INTO t1 VALUES (1, 1), (1, 2), (2, 1), (2, 2), (3, 1);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN FORMAT=JSON SELECT DISTINCT SUM(b) s FROM t1 GROUP BY a ORDER BY s;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "3.06"
|
|
} /* cost_info */,
|
|
"ordering_operation": {
|
|
"using_filesort": true,
|
|
"duplicates_removal": {
|
|
"using_filesort": false,
|
|
"grouping_operation": {
|
|
"using_temporary_table": true,
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"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": "80"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
] /* used_columns */
|
|
} /* table */
|
|
} /* grouping_operation */
|
|
} /* duplicates_removal */
|
|
} /* ordering_operation */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select distinct sum(`test`.`t1`.`b`) AS `s` from `test`.`t1` group by `test`.`t1`.`a` order by `s`
|
|
FLUSH STATUS;
|
|
SELECT DISTINCT SUM(b) s FROM t1 GROUP BY a ORDER BY s;
|
|
s
|
|
1
|
|
3
|
|
SHOW SESSION STATUS WHERE (Variable_name LIKE 'Sort_%' OR Variable_name LIKE 'Created_%_tables') AND Value > 0;
|
|
Variable_name Value
|
|
Created_tmp_tables 1
|
|
Sort_rows 3
|
|
Sort_scan 1
|
|
DROP TABLE t1;
|
|
# "buffer_result" node
|
|
CREATE TABLE t1 (a INT NOT NULL);
|
|
CREATE TABLE t2 (a INT NOT NULL, PRIMARY KEY (a));
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO t2 VALUES (1),(2);
|
|
ANALYZE TABLE t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
EXPLAIN FORMAT=JSON SELECT SQL_BIG_RESULT DISTINCT t1.a FROM t1,t2 ORDER BY t2.a;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "7.34"
|
|
} /* cost_info */,
|
|
"ordering_operation": {
|
|
"using_temporary_table": true,
|
|
"using_filesort": true,
|
|
"duplicates_removal": {
|
|
"using_temporary_table": true,
|
|
"using_filesort": false,
|
|
"cost_info": {
|
|
"sort_cost": "2.00"
|
|
} /* cost_info */,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 1,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.51",
|
|
"eval_cost": "0.10",
|
|
"prefix_cost": "2.61",
|
|
"data_read_per_join": "8"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a"
|
|
] /* used_columns */
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "index",
|
|
"key": "PRIMARY",
|
|
"used_key_parts": [
|
|
"a"
|
|
] /* used_key_parts */,
|
|
"key_length": "4",
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 2,
|
|
"filtered": "100.00",
|
|
"using_index": true,
|
|
"distinct": true,
|
|
"using_join_buffer": "Block Nested Loop",
|
|
"cost_info": {
|
|
"read_cost": "2.53",
|
|
"eval_cost": "0.20",
|
|
"prefix_cost": "5.34",
|
|
"data_read_per_join": "16"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a"
|
|
] /* used_columns */
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* duplicates_removal */
|
|
} /* ordering_operation */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select distinct sql_big_result `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` order by `test`.`t2`.`a`
|
|
DROP TABLE t1, t2;
|
|
#
|
|
CREATE TABLE t1 (a INT NOT NULL, b INT, PRIMARY KEY (a));
|
|
CREATE TABLE t2 (a INT NOT NULL, PRIMARY KEY (a));
|
|
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
|
|
INSERT INTO t2 VALUES (2), (3), (4), (5);
|
|
ANALYZE TABLE t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t1 WHERE t1.b <> 30);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "4.00"
|
|
} /* cost_info */,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"possible_keys": [
|
|
"PRIMARY"
|
|
] /* possible_keys */,
|
|
"rows_examined_per_scan": 4,
|
|
"rows_produced_per_join": 3,
|
|
"filtered": "75.00",
|
|
"cost_info": {
|
|
"read_cost": "2.65",
|
|
"eval_cost": "0.30",
|
|
"prefix_cost": "2.95",
|
|
"data_read_per_join": "48"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
] /* used_columns */,
|
|
"attached_condition": "(`test`.`t1`.`b` <> 30)"
|
|
} /* table */
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "eq_ref",
|
|
"possible_keys": [
|
|
"PRIMARY"
|
|
] /* possible_keys */,
|
|
"key": "PRIMARY",
|
|
"used_key_parts": [
|
|
"a"
|
|
] /* used_key_parts */,
|
|
"key_length": "4",
|
|
"ref": [
|
|
"test.t1.a"
|
|
] /* ref */,
|
|
"rows_examined_per_scan": 1,
|
|
"rows_produced_per_join": 3,
|
|
"filtered": "100.00",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "0.75",
|
|
"eval_cost": "0.30",
|
|
"prefix_cost": "4.00",
|
|
"data_read_per_join": "24"
|
|
} /* cost_info */,
|
|
"used_columns": [
|
|
"a"
|
|
] /* used_columns */
|
|
} /* table */
|
|
}
|
|
] /* nested_loop */
|
|
} /* query_block */
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`b` <> 30))
|
|
DROP TABLE t1, t2;
|
|
set default_storage_engine= @save_storage_engine;
|
|
set optimizer_switch=default;
|