MYSQL insert limit analysis

Source: Internet
Author: User
Tags mysql insert

1. 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 ".

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

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

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

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

1). Select create table to CREATE a 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 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, use myisampack to compress it. See section 15.1.3.3 "compression table feature ".
6) Use myisamchk-r-q/path/to/db/tbl_name to re-create the 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 or the mysqladmin flush-tables command.

6. 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.