How do I quickly add large amounts of data to MySQL?
When you’re adding large amounts of data to MySQL — more than, say, 25,000 rows at once — it’s important to do it efficiently.
If you restore a database file created with phpMyAdmin or mysqldump from our backups, the exported file will already contain commands to do most of the things described below, so you don’t need to worry about it separately.
But if you’re creating the database export “dump” file yourself, these tips will 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 our system will probably stop the import.
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.
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.