Optimizing Updates, Deletes, and Inserts

An UPDATE is basically the same as a SELECT, but with a write operation afterward. For example, for the purposes of optimizing, the following code:

UPDATE fieldname FROM tablename WHERE condition

is the same as this code:

SELECT fieldname FROM tablename WHERE condition

You can optimize an UPDATE statement in the same way as you would the equivalent SELECT statement. Also note that the fewer indexes you use and the smaller the data, the quicker the operation will be. Take care not to use superfluous indexes or make the fields or indexes larger than they need to be.

The speed of a DELETE statement depends on the number of indexes. When deleting, each record needs to be deleted from any associated indexes as well as the main data file. This is the reason that TRUNCATE tablename is much faster than DELETE tablename, as the entire table is dropped at once, without the need to delete each individual index and data record.

The best method to insert data is to use LOAD DATA rather than INSERT, as this is up to 20 times faster.

You can speed up the process by disabling keys for the duration of the period you're adding data. MySQL will then only have to concentrate on adding the data and not worry about adding to the index files at the same time. The data itself is then added much quicker, and when the indexes are built separately, the process will be more optimal as well. You can use the following procedure:

ALTER TABLE tbl_name DISABLE KEYS; LOAD DATA INFILE filename INTO TABLE tablename ALTER TABLE tbl_name ENABLE KEYS;

You're not always going to be able to insert from a text file, though. But if you can group your inserts, multiple value lists are added much quicker than the separate statements. For instance, the following query:

INSERT INTO tablename VALUES(record1),(record2)…(recordn);

is much faster than this alternative:

INSERT INTO tablename VALUES(record1); INSERT INTO tablename VALUES(record1); … INSERT INTO tablename VALUES(recordn);

The reason for this is that the indexes are only flushed once per INSERT statement. If you require multiple INSERT statements, you can use locking to achieve the same result. For nontransactional tables, use statements like this:

LOCK TABLES tablename WRITE; INSERT INTO tablename VALUES (record1),(record2),(record3); INSERT INTO tablename VALUES (record4),(record5),(record6); UNLOCK TABLES;

Be aware that no one will be able to read your tables while the previous statements are in progress.

To achieve the same with transactional tables, use these statements instead:

BEGIN; INSERT INTO tablename VALUES (record1),(record2),(record3); INSERT INTO tablename VALUES (record4),(record5),(record6); COMMIT;

Matters become slightly more complex when you're adding records from many threads. Consider a scenario where the first thread adds 10,000 records, and the second thread adds one record. Using locking, the overall speed will be a lot faster, but the second thread will only complete after the first thread. Without locking, the second thread will complete much more quickly, but the overall speed will be slower. The importance of the second thread relative to the first will determine which method you choose.

However, your application may need to do many unrelated inserts continually. If you're not using row-level locking (such as is possible with InnoDB tables), you may find that the vast hordes of people querying your tables are waiting an inordinately long time for the few to carry out their inserts. But don't despair—there are ways to minimize this effect.

The first is to use INSERT LOW PRIORITY. This causes the insert to lose its usual pushy behavior and to wait until there are no more read queries waiting in the queue. The problem, though, is that if your database is very busy, the client performing the INSERT LOW PRIORITY may take a long time to find a gap, if ever!

An alternative here is INSERT DELAYED. The client is immediately freed, and the insert put into a queue (with all the other INSERT DELAYED statements still waiting for the queue to end). A downside of this is that no meaningful information is passed back to the client (such as the auto_increment value), as the INSERT has not been processed when the client is freed. But some things aren't worth waiting for! Also, be aware that there is the possibility of all inserts in the queue being lost if there is a catastrophe, such as a power failure.

Warning 

For neither INSERT LOW PRIORITY nor INSERT DELAYED do you have any idea when the data will be inserted, if at all, so use them with caution.