INSERT statement speed

INSERT statement speed

The time required to insert a record is composed of the following factors, and the number indicates the approximate proportion:

• Connection: (3)
• Send query to server: (2)
• Analysis query: (2)
• Insert record: (1x record size)
• Insert index: (1x index)
• Close: (1)

This does not take into account the initial overhead of opening a table. Each concurrent query is opened.

The table size is as follows:N(B tree) speed slows down index insertion.

Some methods to accelerate insertion:

· If many rows are inserted from the same client at the same time, use the INSERT statement containing multiple values to INSERT several rows at the same time. This is faster than using a single-row INSERT Statement (several times faster in some cases ). If you add data to a non-empty table, you can adjust the bulk_insert_buffer_size variable to make data insertion faster. See section 5.3.3 "server system variables ".

· If you INSERT many rows from different clients, you can use the insert delayed statement to speed up the process. See section 13.2.4 "INSERT Syntax ".

· MyISAM is used. If no row is deleted in the table, the row can be inserted while the SELECT statement is running.

· When loading a table from a text file, use load data infile. This is usually 20 times faster than using many INSERT statements. See section 13.2.5 "load data infile Syntax ".

· When a table has many indexes, it is possible to do more work to make load data infile faster. Use the following process:

1. You can use create table to CREATE a TABLE.
2. Execute the flush tables statement or commandMysqladmin flush-tables.
3. UseMyisamchk -- keys-used = 0-rq/Path/to/db/tbl_name. This removes all indexes from the table.
4. Use load data infile to insert DATA into the table, because no index is updated, so it is very fast.
5. If you only want to read the table later, useMyisampackCompress it. See section 15.1.3.3 "compression table feature ".
6. UseMyisamchk-r-q/Path/to/db/tbl_nameRe-create an index. This will create an index tree in memory before writing data to the disk, and it is faster, because it avoids a large number of disk searches. The Results Index Tree is also perfectly balanced.
7. Execute the flush tables statement orMysqladmin flush-tablesCommand.

Note that if you insert an empty MyISAM table, load data infile can also perform the preceding optimization. The main difference is thatMyisamchkAllocate more temporary memory for index creation, which is more allocated than the server re-create index when the load data infile statement is executed.

You can also use ALTER TABLETbl_nameDISABLE KEYSMyisamchk -- keys-used = 0-rq/Path/to/db/tbl_name, Use ALTER TABLETbl_nameENABLE KEYSMyisamchk-r-q/Path/to/db/tbl_name. In this way, you can skip flush tables.

· Locking a table can accelerate the INSERT operation with multiple statements:

• Lock tables a WRITE;
• Insert into a VALUES );
• Insert into a VALUES (8, 26), (6, 29 );
• Unlock tables;

This improves the performance because the index cache is refreshed to the disk only once after all INSERT statements are completed. Generally, the number of INSERT statements is the index cache refresh. If you can use one statement to insert all rows, you do not need to lock them.

For transaction TABLES, BEGIN and COMMIT should be used instead of lock tables to speed up insertion.

Locking also reduces the overall time for multi-connection testing, although the maximum wait time for them to wait for locking will increase. For example:

Connection 1 does 1000 inserts

Connections 2, 3, and 4 do 1 insert

Connection 5 does 1000 inserts

If no lock is used, 2, 3, and 4 are completed before 1 and 5. If locking is used, 2, 3, and 4 may not be completed before 1 or 5, but the overall time should be about 40% faster.

INSERT, UPDATE, and DELETE operations are performed inMySQLIs very fast, by locking more than five consecutive insert or update operations in a row, you can achieve better overall performance. If you insert a table multiple times in a row, you can execute lock tables and then immediately execute unlock tables (about every 1000 rows) to allow other threads to access the table. This will also achieve good performance.

INSERT loading DATA is much slower than load data infile, even if the above policy is used.

· To speed up load data infile and INSERT in the MyISAM table, increase the key-speed buffer by adding the key_buffer_size system variable. See section 7.5.2 "Adjust server Parameters ".