In MYSQL, as well as most database engines, restoring a backup, or importing from an existing dump file can take a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:
SET AUTOCOMMIT = 0;
.. your dump file ..
SET FOREIGN_KEY_CHECKS = 1;
SET AUTOCOMMIT = 1;
This will force MySQL to not commit until all rows have been loaded, as well as skip all foreign keys checks. Only skip these checks if you are 100% sure that no constraint is violated. This will usually be the case when dumping from one table and inserting into another.
- Return of Blue Meanie – HD Video on SmugMug
- Show Processlist and Show Full Processlist – MySQL Tip