|
The EXPLAIN command suppresses the Using temporary Extra information in tabular query execution plan in some situations.
Example:
explain SELECT Code, Name FROM Country UNION SELECT CountryCode, Name FROM City;
|
+------+--------------+------------+------+---------------+------+---------+------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+------------+------+---------------+------+---------+------+------+-------+
|
| 1 | PRIMARY | Country | ALL | NULL | NULL | NULL | NULL | 239 | |
|
| 2 | UNION | City | ALL | NULL | NULL | NULL | NULL | 4188 | |
|
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
|
+------+--------------+------------+------+---------------+------+---------+------+------+-------+
|
The UNION RESULT should indicate a temporary table.
Prove 1 that it should:
SQL> show session status like 'created_tmp_tables';
|
+--------------------+-------+
|
| Variable_name | Value |
|
+--------------------+-------+
|
| Created_tmp_tables | 5 |
|
+--------------------+-------+
|
Increases by one every time the query is executed.
Prove 2: The QEP of a competitive product also indicates the use of a temporary table:
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|
| 1 | PRIMARY | Country | NULL | ALL | NULL | NULL | NULL | NULL | 239 | 100.00 | NULL |
|
| 2 | UNION | City | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 100.00 | NULL |
|
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
|
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|
Prove 3:
select digest_text, SUM_CREATED_TMP_TABLES from events_statements_summary_by_digest where digest = '60efda9a0b1336d8bdffe911f1d654ab';
|
+----------------------------------------------------------------------------------+------------------------+
|
| digest_text | SUM_CREATED_TMP_TABLES |
|
+----------------------------------------------------------------------------------+------------------------+
|
| SELECT CODE , NAME FROM `Country` UNION SELECT `CountryCode` , NAME FROM `City` | 4 |
|
+----------------------------------------------------------------------------------+------------------------+
|
|