CODEWITHSUNDEEP
mysqlwindowslinux
Lars Steen
Lars Steen

Reputation: 759

Why is import of SQL so slow?

I have an SQL file containing two tables with around 600,000 rows altogether. Yesterday, I tried to import the file into my MySQL database on Fedora 16, and it took over 2 hours to import the file. On my Windows PC it took 7 minutes. My Linux and Windows machines have exactly the same hardware. A couple of my friends tried it too, and they had a similar experience.

The command we were using was: mysql -u root database_name < sql_file.sql.

Why is there such a difference in speed?

Upvotes: 43

Views: 47566

Answers (2)

Mahesh Patil
Mahesh Patil

Reputation: 1551

Why don't you export .sql file as BULK INSERT option and import it, try these options while taking a backup using mysqldump

--extended-insert: use multiple-row insert statements

--quick: do not do buffering of row data, good if tables are large

Note: Make sure you should increase value of max_allowed_packet=32M or more in my.cnf file before generating .sql file.

Upvotes: 6

David Schwartz
David Schwartz

Reputation: 182779

My bet is that Fedora 16 is honoring the transaction/sync semantics and Windows is not. If you do the math, 600,000 updates in two hours is 5,000 per minute. That's the same order of magnitude as a disk's rotation rate.

You can try adding SET autocommit=0; to the beginning of your import file and COMMIT; to the end. See this page for more information.

Upvotes: 78

Related Questions