Support Start > Web Hosting Technical Information > MySQL Databases > Large MySQL Imports

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
• Tips for MyISAM tables
• Tips for InnoDB tables
• 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 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.

Need help?

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.