How do I quickly add large amounts of data to MySQL?
When you’re adding large amounts of data to MySQL — more than a thousand rows at once — it’s important to do it efficiently.
- Disable MySQL indexes
- Tips for MyISAM tables
- Tips for InnoDB tables
- Don’t swamp the servers
- Need help?
Disable MySQL indexes
You should turn off index updates until the import is done. If you disable index updates, MySQL can import many rows with a single disk write; if you don’t, MySQL will do many separate disk writes for each row.
Disabling indexes makes the import go many, many times faster. It also minimizes the impact on other customers using the server. If you don’t disable indexes during a large import, it will probably cause server load problems, and we’ll probably stop the import.
(Note that if you restore a database file created with phpMyAdmin or mysqldump, the file will already contain commands to disable the indexes, so you don’t need to worry about it separately.)
To disable MySQL indexes, make sure your file contains these commands before the import starts (replacing "table_name" with the real name of your table):
ALTER TABLE `table_name` DISABLE KEYS;
Then send these after the import:
ALTER TABLE `table_name` ENABLE KEYS;
That will make your import run much faster.
Tips for MyISAM tables
With MySQL’s standard MyISAM table type, you should use MySQL's INSERT DELAYED command instead of the normal INSERT. It's not a perfect solution, but it can encourage MySQL to write the data to the disk when the database is idle.
Tips for InnoDB tables
If you use InnoDB tables instead of MyISAM tables, add these extra commands at the top of the file to avoid a great deal of disk access:
SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0; SET AUTOCOMMIT = 0;
And add these at the end:
SET UNIQUE_CHECKS = 1; SET FOREIGN_KEY_CHECKS = 1; COMMIT;
For maximum effect, you should add this in addition to “DISABLE KEYS” and “ENABLE KEYS” described above.
Don’t swamp the servers
If you have an import that will take more than a minute or so, make sure it doesn’t simply try to blast the data into MySQL as quickly as possible. That would cause MySQL to try dedicate all the server’s available power to your disk writes. That will slow down other connections and probably cause our systems to stop the import.
Instead, try to make sure you use less than 25% of the server’s resources. You could do this by measuring how long it takes to import the first thousand rows, for example, then making sure you delay your import script by three times that long after every thousand rows.
If you have any doubts about how to do this or you need help, please contact us and we’ll be glad to assist you.