MySQL manual version 5.0.20-MySQL optimization (IV) (1) (3)

7.2.12 accelerate INSERT

The time taken to insert a record is determined by the following factors, and the following figures roughly indicate the proportion of the impact:

Connection: (3)

Send query to server: (2)

Resolution query: (2)

Insert Record: (1 x record size)

Insert index: (1 x number of indexes)

Close: (1)

The overhead of opening the data table during initialization is not considered here, because each query is performed only once.

If it is a B-tree index, as the number of indexes increases, the speed of inserting records decreases in the proportion of log N.

You can use the following methods to increase the insert speed:

If you want to INSERT many records at the same time on the same client, you can use the INSERT statement with multiple VALUES. This method is much faster (in some cases, it is faster) than an INSERT statement that uses a single value ). If you want to add a record to a non-empty data table, you can adjust the value of the variable bulk_insert_buffer_size to make it faster. For more information, see "5.2.3 Server System Variables ".

If you want to INSERT a large number of records from different clients, you can use the insert delayed statement to increase the speed. For details, see "14.1.4 INSERT Syntax ".

For MyISAM, you can insert records when the SELECT statement is running, as long as the records are not deleted at this time.

To LOAD a text file to a DATA table, you can use load data infile. This is usually 20 times the use of a large number of INSERT statements. For details, see "14.1.5 load data infile Syntax ".