神刀安全网

MySQL/MariaDB cursors and temp tables

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 and LIMIT 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

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » MySQL/MariaDB cursors and temp tables

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址