6

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.

  1. 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;
    
  2. 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;
    
2

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

3

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.

  • Can you please explain this "Option 1 is slow because it is going back and forth to the server." isn't a stored procedure like Option 1 suppose to run completely at the server ? – Accountant م Apr 8 '18 at 11:56
  • 1
    @Accountantم - valid point. I modified my answer. – Rick James Apr 8 '18 at 12:16
  • @Accountantم -- Out of curiosity, what is MEEM for? – Rick James Apr 9 '18 at 0:48
  • Thank you very much for the answer and the edit. It's the first Arabic letter of my name محمد Muhammad. – Accountant م Apr 9 '18 at 11:39
  • 1
    @Accountantم - "legend" -- I like it. My tips: mysql.rjweb.org Much of what I write about is directed at performance and scaling. – Rick James Apr 9 '18 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.