I didn’t know you could actually do this before addressing this problem in a benchmark using 5.0.36.
MySQL allows you to update two tables with a single UPDATE statement. I knew you could reference two tables in an UPDATE statement but not update both. However when working with a client after benchmarking I observed a large number of Created_tmp_disk_tables via SHOW GLOBAL STATUS and found that this query was the offending query only for certain circumstances. Understanding took a little longer.
schema.sql
DROP TABLE IF EXISTS a; CREATE TABLE a( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, i INT UNSIGNED NOT NULL, c CHAR(1) NOT NULL) ENGINE=INNODB; DROP TABLE IF EXISTS b; CREATE TABLE b( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, i INT UNSIGNED NOT NULL, c CHAR(1) NOT NULL, t TEXT NOT NULL) ENGINE=INNODB;
data.sql
INSERT INTO a VALUES(1,1,'a');
INSERT INTO a VALUES(2,2,'b');
INSERT INTO a VALUES(3,3,'c');
INSERT INTO a VALUES(4,4,'d');
INSERT INTO b VALUES(1,1,'a',REPEAT('The quick brown fox jumped over the lazy dog',100));
INSERT INTO b VALUES(2,2,'b',REPEAT('The quick brown fox jumped over the lazy dog',200));
INSERT INTO b VALUES(3,3,'c',REPEAT('The quick brown fox jumped over the lazy dog',300));
INSERT INTO b VALUES(4,4,'d',REPEAT('The quick brown fox jumped over the lazy dog',400));
test.sql
SHOW SESSION STATUS LIKE '%tmp%tables';
SHOW SESSION STATUS LIKE '%tmp%tables';
UPDATE a,b SET a.i = 10, a.c = 'x', b.i = 20 WHERE a.id = b.id and a.id=1;
SHOW SESSION STATUS LIKE '%tmp%tables';
UPDATE a,b SET a.i = 10, a.c = 'x', b.i = 20,b.t=REPEAT('b',5000) WHERE a.id = b.id and a.id=3;
SHOW SESSION STATUS LIKE '%tmp%tables';
UPDATE b SET t = REPEAT('a',5000) WHERE b.id=2;
SHOW SESSION STATUS LIKE '%tmp%tables';
Output
The following output shows the following.
- Impact of a SHOW SESSION STATUS increments Created_tmp_tables
- A two table update increments Created_tmp_tables
- A two table update including a text field increments Created_tmp_tables and Created_tmp_disk_tables
- A single table update with the text field increments Created_tmp_tables
So the culprit was the combination of a two table update and a TEXT field.
mysql> source test.sql
--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 3 |
+-------------------------+-------+
2 rows in set (0.00 sec)
--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 4 |
+-------------------------+-------+
2 rows in set (0.00 sec)
--------------
UPDATE a,b SET a.i = 10, a.c = 'x', b.i = 20 WHERE a.id = b.id and a.id=1
--------------
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 6 |
+-------------------------+-------+
2 rows in set (0.00 sec)
--------------
UPDATE a,b SET a.i = 10, a.c = 'x', b.i = 20,b.t=REPEAT('b',5000) WHERE a.id = b.id and a.id=3
--------------
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1 |
| Created_tmp_tables | 8 |
+-------------------------+-------+
2 rows in set (0.00 sec)
--------------
UPDATE b SET t = REPEAT('a',5000) WHERE b.id=2
--------------
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1 |
| Created_tmp_tables | 9 |
+-------------------------+-------+
2 rows in set (0.00 sec)