13.3.2 Optimizing Updates - MySQL

The optimizations discussed so far have been shown for SELECT statements, but optimization techniques can be used for statements that update tables, too:

• For a DELETE or UPDATE statement that uses a WHERE clause, try to write it in a way that allows an index to be used for determining which rows to delete or update. Techniques for this were discussed earlier for SELECT statements; they apply to DELETE or UPDATE as well.

• EXPLAIN is used with SELECT queries, but you might also find it helpful for analyzing UPDATE and DELETE queries. Write a SELECT statement that has the same WHERE clause as the UPDATE or DELETE and analyze that.

• Use multiple-row INSERT statements instead of multiple single-row INSERT statements. For example, instead of using three single-row statements like this:

  mysql> INSERT INTO t (id, name)
  
  VALUES(1,'Bea');
  mysql> INSERT INTO t (id, name)
  
  VALUES(2,'Belle');
  mysql> INSERT INTO t (id, name)
  
  VALUES(3,'Bernice');

  You could use a single multiple-row statement that does the same thing:

  mysql> INSERT INTO t (id, name)
  
  VALUES(1,'Bea'),(2,'Belle'),(3,'Bernice');

  The multiple-row statement is shorter, which is less information to send to the server. More important, it allows the server to perform all the updates at once and flush the index a single time, rather than after each of the individual inserts. This optimization can be used with any storage engine.

  If you're using an InnoDB table, you can get better performance even for single-row statements by grouping them within a transaction rather than by executing them with autocommit mode enabled:

  mysql> BEGIN;
  mysql> INSERT INTO t (id, name)
  
  VALUES(1,'Bea');
  mysql> INSERT INTO t (id, name)
  
  VALUES(2,'Belle');
  mysql> INSERT INTO t (id, name)
  
  VALUES(3,'Bernice');
  mysql> COMMIT;

  Using a transaction allows InnoDB to flush the changes at commit time. In autocommit mode, InnoDB flushes the changes after each insert.

• For any storage engine, LOAD DATA INFILE is even faster than multiple-row INSERT statements. For MyISAM in particular, if you're loading an empty table, MySQL will even automatically disable updating nonunique indexes during the load operation to speed it up more.

• To replace existing rows, use REPLACE rather than DELETE plus INSERT.