// 5.6.21 mysql> show variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.6.21 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.21 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.5 | +-------------------------+------------------------------+ 7 rows in set (0.00 sec) mysql> create table ten(a int); Query OK, 0 rows affected (0.03 sec) mysql> insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> mysql> create table t1 (a int, b int, c int); Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 select a,a,a from ten; Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> mysql> create table t2 as select * from t1; Query OK, 10 rows affected (0.02 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> 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": "(`test`.`t2`.`a` = `test`.`t1`.`a`)" } } ] } } } } 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select distinct `test`.`t1`.`c` AS `c`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) order by `test`.`t1`.`b`,`test`.`t2`.`b` | +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> select distinct t1.c, t2.c from t1, t2 where t1.a=t2.a order by t1.b, t2.b\G *************************** 1. row *************************** c: 0 c: 0 *************************** 2. row *************************** c: 1 c: 1 *************************** 3. row *************************** c: 2 c: 2 *************************** 4. row *************************** c: 3 c: 3 *************************** 5. row *************************** c: 4 c: 4 *************************** 6. row *************************** c: 5 c: 5 *************************** 7. row *************************** c: 6 c: 6 *************************** 8. row *************************** c: 7 c: 7 *************************** 9. row *************************** c: 8 c: 8 *************************** 10. row *************************** c: 9 c: 9 10 rows in set (0.00 sec) mysql> show status like '%tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 0 | | Created_tmp_tables | 1 | +-------------------------+-------+ 3 rows in set (0.00 sec) // 5.6.22 mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.6.22 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.22-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> create table ten(a int); Query OK, 0 rows affected (0.03 sec) mysql> insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> mysql> create table t1 (a int, b int, c int); Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 select a,a,a from ten; Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> mysql> create table t2 as select * from t1; Query OK, 10 rows affected (0.03 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> 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": "(`test`.`t2`.`a` = `test`.`t1`.`a`)" } } ] } } } } 1 row in set, 1 warning (0.03 sec) mysql> mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> select distinct t1.c, t2.c from t1, t2 where t1.a=t2.a order by t1.b, t2.b\G *************************** 1. row *************************** c: 0 c: 0 *************************** 2. row *************************** c: 1 c: 1 *************************** 3. row *************************** c: 2 c: 2 *************************** 4. row *************************** c: 3 c: 3 *************************** 5. row *************************** c: 4 c: 4 *************************** 6. row *************************** c: 5 c: 5 *************************** 7. row *************************** c: 6 c: 6 *************************** 8. row *************************** c: 7 c: 7 *************************** 9. row *************************** c: 8 c: 8 *************************** 10. row *************************** c: 9 c: 9 10 rows in set (0.02 sec) mysql> show status like '%tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 0 | | Created_tmp_tables | 1 | +-------------------------+-------+ 3 rows in set (0.00 sec) // 5.7.6 mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.7.6 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.7.6-m16-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> create table ten(a int); Query OK, 0 rows affected (0.03 sec) mysql> insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> mysql> create table t1 (a int, b int, c int); Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 select a,a,a from ten; Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> mysql> create table t2 as select * from t1; Query OK, 10 rows affected (0.03 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> 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, "cost_info": { "query_cost": "24.00" }, "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_examined_per_scan": 10, "rows_produced_per_join": 10, "filtered": 100, "cost_info": { "read_cost": "1.00", "eval_cost": "2.00", "prefix_cost": "3.00", "data_read_per_join": "160" }, "used_columns": [ "a", "b", "c" ] } }, { "table": { "table_name": "t2", "access_type": "ALL", "rows_examined_per_scan": 10, "rows_produced_per_join": 10, "filtered": 10, "using_join_buffer": "Block Nested Loop", "cost_info": { "read_cost": "1.00", "eval_cost": "2.00", "prefix_cost": "24.00", "data_read_per_join": "160" }, "used_columns": [ "a", "b", "c" ], "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)" } } ] } } } } 1 row in set, 1 warning (0.00 sec) mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> select distinct t1.c, t2.c from t1, t2 where t1.a=t2.a order by t1.b, t2.b\G *************************** 1. row *************************** c: 0 c: 0 *************************** 2. row *************************** c: 1 c: 1 *************************** 3. row *************************** c: 2 c: 2 *************************** 4. row *************************** c: 3 c: 3 *************************** 5. row *************************** c: 4 c: 4 *************************** 6. row *************************** c: 5 c: 5 *************************** 7. row *************************** c: 6 c: 6 *************************** 8. row *************************** c: 7 c: 7 *************************** 9. row *************************** c: 8 c: 8 *************************** 10. row *************************** c: 9 c: 9 10 rows in set (0.00 sec) mysql> show status like '%tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 0 | | Created_tmp_tables | 1 | +-------------------------+-------+ 3 rows in set (0.00 sec)