| If you frequently have many rows of data to insert into a table and find that performance starts to be an issue, here are some tips to help. First, there are different techniques to use depending on if you are inserting many rows at once or inserting many rows over a period of time but have many reads interspersed with the writes. For inserting many rows of data at once, try these optimizations: If possible, use LOAD DATA INFILE (see the phrase "Importing Data from Text Files" in this chapter). It provides a performance benefit by reducing the amount of work that both the client and server must do to prepare the data for insertion into a table. Use the ALTER TABLE command to temporarily disable index creation when inserting large amounts of data. This allows the work of building the table indexes to be done in one efficient step after the data is inserted. For example: ALTER TABLE table_name DISABLE KEYS; # Many INSERT statements here ALTER TABLE table_name ENABLE KEYS; For MyISAM tables, lock the table while inserting data to prevent reads from the table from slowing down the writes. You can use the following code: LOCK TABLES table_name WRITE; # Many INSERT statements here UNLOCK TABLES; For InnoDB tables, run all of your INSERT statements within a transaction with the following code: BEGIN; # Many INSERT statements here COMMIT; Use a multivalue INSERT. These types of statements reduce the amount of work that the client and server must do to process a query before the data is written to a table. See the phrase "Adding Many Rows in One Query Using INSERT" in this chapter.
For inserting many rows of data interspersed with many reads, try these optimizations: For MyISAM tables, enable the DELAY_KEY_WRITE table option. This option reduces the number of disk writes that MySQL has to make when creating new entries in a table's index. You can set the option on an existing table using ALTER TABLE as in the following code: ALTER TABLE table_name DELAY_KEY_WRITE = 1; Caution If the MySQL server crashes, tables that have DELAY_KEY_WRITE set might have incomplete indexes. To ensure that the indexes are complete, you should restart the MySQL server with --myisam-recover=BACKUP,FORCE.
Use InnoDB tables. They handle many concurrent reads and writes better than MyISAM tables. Don't write to the table you read from. Instead, create a table for handling writes and then merge the rows from the write table into your read table at a regular interval using the bulk insert tips mentioned previously to get better performance.
There are many additional ways to squeeze performance out of MySQL. Some options include hardware configuration choices, server configuration, table design, SQL optimizations, and so on. For more information, refer to the online manual. |
