Unreasonable difference between MySQL dump and load times

Question

I'm dumping a database into a file (piping the dump through gzip) and it takes about 2 minutes to create a 300MB dump.sql.gz file.

When loading the file back into MySQL (the dump begins with dropping the tables), the load takes around 30 minutes!!

What can be the reason for such a huge difference in times? Are there any general tips for drastically reducing the load times?

Thanks.

Answer 1

One reason is that writing to a database is MUCH more expansive than reading from it. The database has to make sure writes are transactional, it has to update indexes, expand files, and so on.

Another resaon is that MySQL dumps a SQL version of your data, like a ton of insert statements in a big text file. That's very slow. Backups from other databases are usually binary files that can be written over the original database, which is faster than restoring one row at a time.

Answer 2

In addition to Andomar's answer - you'll get a significant improvement adding '--delayed-insert' and '--extended-insert' to the dump.