In MariaDB and MySQL, cursors create a temporary table.
Does this statement deserve a whole blog post? Apparently not. However, in some cases one does not expect a temporary table to be created:
SELECT ... FOR UPDATE
: An exclusive lock is created, yes, but you still read data from a temporary table.SELECT FROM
a temporary table: you are reading from a temporary tables, yes, but an internal temporary table is created anyway.Impossible WHERE
andLIMIT 0
.
A quick example:
CREATE TEMPORARY TABLE t
(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);
CREATE PROCEDURE p()
BEGIN
DECLARE c CURSOR FOR
SELECT id FROM t WHERE 0 LIMIT 0 FOR UPDATE;
OPEN c;
CLOSE c;
END;
MySQL [test]> SHOW STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| Created_tmp_tables | 31 |
+--------------------+-------+
1 row in set (0.00 sec)
MySQL [test]> CALL p();
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> SHOW STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| Created_tmp_tables | 32 |
+--------------------+-------+
1 row in set (0.00 sec)
I am not complaining, and I don’t even know if this behavior can be changed. But one should certainly be aware of this behavior. For example, one could think that creating a temporary table one time and then loop on that table with cursors several times is an optimization – but that’s not the case.
Federico