Description:
EXPLAIN FORMAT=JSON shows that two temporary tables will be used, while query execution shows that just one table is used.
How to repeat:
Run:
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, b int, c int);
insert into t1 select a,a,a from ten;
create table t2 as select * from t1;
explain format=json
select distinct t1.c, t2.c from t1, t2 where t1.a=t2.a order by t1.b, t2.b\G
MySQL [test3]> explain format=json
-> select distinct t1.c, t2.c from t1, t2 where t1.a=t2.a order by t1.b, t2.b\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"ordering_operation": {
"using_temporary_table": true,
"using_filesort": true,
"duplicates_removal": {
"using_temporary_table": true,
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows": 10,
"filtered": 100
}
},
{
"table": {
"table_name": "t2",
"access_type": "ALL",
"rows": 10,
"filtered": 100,
"using_join_buffer": "Block Nested Loop",
"attached_condition": "(`test3`.`t2`.`a` = `test3`.`t1`.`a`)"
}
}
]
}
}
}
}
The part of output we are interested in is:
"ordering_operation": {
"using_temporary_table": true,
...
"duplicates_removal": {
"using_temporary_table": true,
I interpret that as both query plan elements use temporary tables.
However, query execution shows that only one table is used:
flush status;
MySQL [test3]> select distinct t1.c, t2.c from t1, t2 where t1.a=t2.a order by t1.b, t2.b\G
...
MySQL [test3]> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
Description: EXPLAIN FORMAT=JSON shows that two temporary tables will be used, while query execution shows that just one table is used. How to repeat: Run: create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, c int); insert into t1 select a,a,a from ten; create table t2 as select * from t1; explain format=json select distinct t1.c, t2.c from t1, t2 where t1.a=t2.a order by t1.b, t2.b\G MySQL [test3]> explain format=json -> select distinct t1.c, t2.c from t1, t2 where t1.a=t2.a order by t1.b, t2.b\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "ordering_operation": { "using_temporary_table": true, "using_filesort": true, "duplicates_removal": { "using_temporary_table": true, "using_filesort": false, "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "rows": 10, "filtered": 100 } }, { "table": { "table_name": "t2", "access_type": "ALL", "rows": 10, "filtered": 100, "using_join_buffer": "Block Nested Loop", "attached_condition": "(`test3`.`t2`.`a` = `test3`.`t1`.`a`)" } } ] } } } } The part of output we are interested in is: "ordering_operation": { "using_temporary_table": true, ... "duplicates_removal": { "using_temporary_table": true, I interpret that as both query plan elements use temporary tables. However, query execution shows that only one table is used: flush status; MySQL [test3]> select distinct t1.c, t2.c from t1, t2 where t1.a=t2.a order by t1.b, t2.b\G ... MySQL [test3]> show status like '%tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 0 | | Created_tmp_tables | 1 | +-------------------------+-------+