5 ways to optimize mysql inserts

The following are five ways to improve queries involving table inserts:

1) use LOAD DATA INFILE when loading data from a text file

This is around 20 times faster than using insert statements.

2) use INSERT statements with multiple VALUES lists to insert several rows at a time

This is many times faster than using separate single-row insert statements. Tuning the bulk_insert_buffer_size variable can also make inserts (to tables that contain rows) even faster.

3) enable concurrent inserts for myisam tables

The concurrent_insert system variable can be set to modify the concurrent-insert processing. By default, the variable is set to 1. If concurrent_inserts is set to 0, concurrent inserts are disabled. If the variable is set to 2, concurrent inserts at the end of the table are allowed even for tables that have deleted rows.

4) use insert delayed

This is useful if you have clients that cannot or need not wait for the insert to complete. This is a common situation when you use MySQL for logging and you also periodically run select and update statements that take a long time to complete. When a client uses insert delayed, the server returns right away, and the row is queued to be inserted when the table is not in use by any other thread. Another benefit of using insert delayed is that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts.

5) lock your tables before inserting (for non-transactional tables)

This benefits performance because the index buffer is flushed to disk only once, after all insert statements have completed. Normally, there would be as many index buffer flushes as there are insert statements. Explicit locking statements are not needed if you can insert all rows with a single insert.

To obtain faster insertions for transactional tables, you should use start transaction and commit instead of lock tables.