Which is faster? Multiple update queries in a cursor loop, or a single query?

Question

Which is faster? Multiple update queries in a cursor loop, or a single query? For example, in this case, which is faster? This query is in a trigger.

Multiple Query in loop

DECLARE done INT DEFAULT FALSE;
DECLARE record_id INT;
DECLARE cur CURSOR FOR SELECT id FROM table1 WHERE column = 0;

OPEN cur;

  users_loop: LOOP

     FETCH cur INTO id;

     IF done THEN
         LEAVE users_loop;
     END IF;

     UPDATE table2 SET column = 0 WHERE id = id;

  END LOOP;

CLOSE cur;

Single Query

 CREATE TEMPORARY TABLE tmp_table (id int(10));

 INSERT INTO tmp_table SELECT id FROM table1 WHERE column = 0;

 UPDATE table2 SET column = 0 WHERE id IN(SELECT id FROM temp_table);

 DROP TABLE IF EXISTS tmp_table;

Rick James · Answer 1 · 2018-04-08T12:16:18.317

Neither -- You need to present a 3rd option.

Option 1 is slow because it is going back and forth between the Stored Routine and the server. (This is not as bad as between the Client and Server, but it is still extra effort.)

Option 2 is slow because of the tmp table and subquery.

Option 3 is something like

UPDATE   table2
    JOIN table1  ON table1.id = table2.id
    SET   table2.column = 0
    WHERE table1.column = 0;

(I assume id is the PRIMARY KEY in each table?)

Important: table1 needs INDEX(column).

See "multi-table update" in the online reference manual.

score 2 · Accepted Answer · edited Apr 13 '17 at 12:42

It depends on a lot of factors:

your table definition
your index definitions
your transaction isolation level
your general database load
the available memory
...

If you have enough memory, processor power and temporary table space then your second statement should be the fastest, but with the drawback of having a lot of locking occurring at once.

Locks Set by Different SQL Statements in InnoDB

Running your first statement will probably incur less locking for all the other users, but then again depends on the transaction isolations levels of the users connected to the database.

Transaction Isolation Levels

As you can see there are a multitude of factors that can impact the execution of an update statement.

Without further details I would prefer to use the second statement for speed and your first statement would be used in a environment under load.

A similar question has been asked here:

How to update a (very) large table without locking in MySQL

Which is faster? Multiple update queries in a cursor loop, or a single query?

2 Answers2