Five tips for optimizing MySQL Insert method

Here are 5 ways to improve the query that involves inserting a table:

1 Download data from text using the load infile this will be 20 times times faster than using the INSERT statement.

2 Inserts a few rows at a time using an INSERT statement with multiple values list this will be several times faster than using a single line INSERT statement. Adjusting the bulk_insert_buffer_size variable also increases the speed (in the table that contains the row).

3 The MyISAM table can be inserted in parallel Concurrent_insert system variables can be set to modify Concurrent-insert processing. The default setting for this variable is 1. If the Concurrent_insert is set to 0, parallel inserts are disabled. If the variable is set to 2, it can be inserted in parallel at the end of the table, even if some of the rows of the table have been deleted.

4 Use insertion delay

This is useful if your client cannot or does not have to wait for the insertion to complete. This is common when you use MySQL to store and run regular select and UPDATE statements that take a long time to complete. When the customer uses the Insert delay, the server returns immediately and the row queue waits to be inserted if the table is not called by another thread. Another benefit of using insert latency is that situations that are inserted from multiple customers are bound and recorded in the same block. This will be much faster than handling multiple standalone inserts.

5 Lock the table before inserting it (only for non transactional-type tables)

This improves database performance because the index buffer only refreshes the disk once after all the INSERT statements have completed. In general, how many insert statements will have a number of secondary index buffer flushes. If you can insert all rows with an INSERT statement, you do not need to use an explicit locking statement.

To insert a transaction table more quickly, you should use the start transaction and the commit statement instead of the lock tables statement.