Description:
I've recently noted unexpected "Using temporary" text in Extra column of the EXPLAIN output for a really simple UPDATE query:
mysql> explain update tr2 set c1 = 'X' where id between 'b' and 'c';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| 1 | UPDATE | tr2 | NULL | range | PRIMARY | PRIMARY | 12 | const | 1 | 100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
1 row in set (0.02 sec)
Surelym, the plan for "equivalent" SELECT is different:
mysql> explain select c1 from tr2 where id between 'b' and 'c';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tr2 | NULL | range | PRIMARY | PRIMARY | 12 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
As soon as I switched to STATEMENT, it disappeared:
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
mysql> set session binlog_format='STATEMENT';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT |
+-----------------+
1 row in set (0.00 sec)
mysql> explain update tr2 set c1 = 'X' where id between 'b' and 'c';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | UPDATE | tr2 | NULL | range | PRIMARY | PRIMARY | 12 | const | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)
One can easily check that temporary table is NOT used in reality:
mysql> explain update tr2 set c1 = 'X' where id between 'b' and 'c';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| 1 | UPDATE | tr2 | NULL | range | PRIMARY | PRIMARY | 12 | const | 1 | 100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
1 row in set (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.03 sec)
mysql> update tr2 set c1 = 'X' where id between 'b' and 'c';
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.00 sec)
I think optimizer misinterprets some internal falgs in this case, those set for row-based replication only. Check comments in https://github.com/facebook/mysql-5.6/issues/163 for more details.
How to repeat:
Start server with binary logging enabled and binlog_format set to ROW, then:
[openxs@fc23 5.7]$ bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.11-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.03 sec)
mysql> use test
Database changed
mysql> CREATE TABLE tr2 (
-> id varchar(10) COLLATE latin1_bin NOT NULL,
-> c1 varchar(10) COLLATE latin1_bin DEFAULT NULL,
-> PRIMARY KEY (id)) engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)
mysql> insert into tr2 values ('a', 'a'), ('b', 'b'), ('c', 'c'), ('d', 'd');
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> explain update tr2 set c1 = 'X' where id between 'b' and 'c';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| 1 | UPDATE | tr2 | NULL | range | PRIMARY | PRIMARY | 12 | const | 1 | 100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
1 row in set (0.02 sec)
mysql> explain select c1 from tr2 where id between 'b' and 'c';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tr2 | NULL | range | PRIMARY | PRIMARY | 12 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
mysql> set session binlog_format='STATEMENT';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT |
+-----------------+
1 row in set (0.00 sec)
mysql> explain update tr2 set c1 = 'X' where id between 'b' and 'c';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | UPDATE | tr2 | NULL | range | PRIMARY | PRIMARY | 12 | const | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)
Suggested fix:
Make sure the query is executed in the same way with any binlog format, and EXPLAIN shows all the details about the execution plan correctly.
In the meantime, document all reasons to get "Using temporary" in Extra column in the manual, as for now https://dev.mysql.com/doc/refman/5.6/en/explain-output.html says:
"Using temporary (JSON: using_temporary_table)
To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently."
