MySQL: Query Optimization Tips Part II

In previous post we dealt with the SELECT statement and WHERE clause optimization. In this post we will see optimization of INSERT statement. SELECT statement is very general for dynamic websites while INSERT is used very often where WRITE option is given. The tips for optimization of INSERT statement are:

  1. REPLACE can be used instead of INSERT to overwrite old rows. It can be done using WHERE clause on some UNIQUE INDEX. REPLACE can do job for both INSERT and UPDATE.
  2. Insert multiple rows using INSERT is very efficient method and very important point for optimization. Inserting single row at a time wastes the time and resource of the server and also increases the disk overheads. Multiple inserts at a time is considerably faster.
    INSERT INTO table VALUES (1,'a'),(2,'b'),(3,'c');
  3. DELAYED keyword with INSERT will push the insert value into buffer to run later in case of WRITE/READ is being done on table. This is very useful for MyISAM where table locks on WRITE/READ. Make sure you don’t require insert values immediately. This can be implement where “Login using Request” is used. It works with MyISAM only. Do not work with INSERT…SELECT.
    INSERT DELAYED table VALUES (1,'a'),(2,'b'),(3,'c');
  4. Priority can also be set for the INSERT statements, LOW_PRIORITY and HIGH_PRIORITY, which is also quite useful for some tasks. Works with MyISAM only.
  5. Using IGNORE keyword with INSERT is like a treat. IGNORE converts error into warnings and completes the query. Eg: If your query has insert value which has repeated unique value IGNORE will keep running the query ignoring the error while normal query would have aborted.
  6. The fastest method to load data in a database is LOAD DATA INFILE. It is usually 20 times faster than INSERT statement.
    LOAD DATA INFILE datafile.txt INTO table

If you want more clear picture, read MySQL manual.

Advertisements