CODEWITHSUNDEEP
mysqlsql
iwazovsky
iwazovsky

Reputation: 1927

MySQL long time of insert queries

I have a big sql dump ~1,3 million of rows. I try to import it through mysql console this way:

source mysql_dump.sql

It goes well on start. It creates new table and etc., but after some time of inserting queries, it takes more and more time to proceed insertion queries.

E.g. every ~1700 records console outputs the results and time consumption for this stack of queries. In the beginning to do ~1700 mysql spends ~0.3 sec. After 5 minutes it takes ~ 1 minute.

What can be done, to make it proceed queries that faster, as in the beginning?

Upvotes: 0

Views: 71

Answers (2)

Pavel Gatnar
Pavel Gatnar

Reputation: 4053

When you do so many inserts, do a commit after every 1000 recs.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

This is a bit long for a comment.

One possibility is indexes. You should drop all the indexes on the table before inserting records. Then add the indexes after all the data is in the table. Index insertions can slow down inserts.

Second, if you want to save all the data in a table, it is better to reload it using load data infile.

Upvotes: 1

Related Questions