The insertion limit of MySQL

1. If you insert many rows from the same client at the same time, insert a few rows using the INSERT statement with multiple value. This is faster than using a single-line INSERT statement (in some cases several times faster). If you are adding data to a non-empty table, you can adjust the bulk_insert_buffer_size variable to make the data insert faster. See section 5.3.3, "Server System Variables".

2. If you insert many rows from different clients, you can speed up the insert delayed statement. See section 13.2, 4, "Insert Syntax".

3. With MyISAM, if there are no rows deleted in the table, the row can be inserted while the SELECT statement is running.

4. When loading a table from a text file, use load DATA INFILE. This is usually 20 times times faster than using many INSERT statements. See section 13.2, 5, "LOAD DATA infile Syntax".

5, when the table has many indexes, it is possible to do more work to make the load DATA infile faster. Use the following procedure:

1). Optionally, create table with creation table.
2). Execute the Flush Tables statement or command mysqladmin flush-tables.
3). Use Myisamchk--keys-used=0-rq/path/to/db/tbl_name. This cancels the use of all indexes from the table.
4). Insert the data into the table with the load data infile, because no indexes are updated, so soon.
5). If you only want to read the table later, use Myisampack to compress it. See section 15.1, 3.3, "Compression table characteristics".
6). Re-create the index with Myisamchk-r-q/path/to/db/tbl_name. This creates an index tree in memory before writing to the disk, and it is faster because a large number of disk searches are avoided. The result index tree is also perfectly balanced.
7). Execute the Flush tables statement or the Mysqladmin flush-tables command.

6. Locking the table can speed up insert operations with multiple statements:

* LOCK TABLES a WRITE;
* INSERT into a VALUES (1,23), (2,34), (4,33);
* INSERT into a VALUES (8,26), (6,29);
* UNLOCK TABLES;

This improves performance because the index buffers are flushed to disk only once after all INSERT statements have completed. How many INSERT statements typically have a flush of index buffers. If you can insert all the rows with a single statement, you do not need to lock.

For a transaction table, you should use begin and commit instead of lock tables to speed up the insertion.

The insertion limit of MySQL