0

I am trying to import a dump file to MySQL through command line. The dump file contains about 2 million entries. To import the data I used the command mysql -u USERNAME -p DATABASE < filename.sql . The problem is that the process started 24 hours ago and it's still going while it imported merely 50% of the data. Is there any option I could add to the command to make the process faster or is there any other command I could use? The dump file is available in .sql and .sql.gz format. I also tried to import the sql.gz file but it gives an error saying that there is an error at line 1 with some quote.

Thanks!

Comforse
  • 2,027
  • 4
  • 25
  • 40
  • 2
    How large is the dump file? – Pekka Apr 28 '13 at 08:46
  • 2
    How many index/uniques/etc. hast the target table? – Sirko Apr 28 '13 at 08:47
  • the sql file is 685 MB, the sql.gz is 70 . There are many tables, not a single one. The biggest table has about 1.5 million records with 4 keys i think – Comforse Apr 28 '13 at 08:51
  • Does the "dump file" contain a single `INSERT` statement with multiple rows in a single `VALUES` clause? Or are there millions of separate `INSERT` statements? In any event, you would probably be better loading this much data with `LOAD DATA` or `mysqlimport` from a data file in delimited format. – eggyal Apr 28 '13 at 08:52
  • When I exported the data I chose to add the INSERT for every row – Comforse Apr 28 '13 at 08:53
  • @eggyal Just our of curiosity (not a DB expert), would you know difference in speed if using LOAD DATA or mysqlimport and why? Would it be trivial or 2x faster or 3x faster? I'm asking cause we import 12GB dumps and it takes forever! I've tried finding a way to delay indexing during insertion till all inserts were done, which I believe which be much faster. But I have not found this feature or proof that this would help. – Jeach Sep 02 '14 at 22:38
  • @Jeach: According to [Speed of `INSERT` Statements](http://dev.mysql.com/doc/en/insert-speed.html) (emphasis added): "*When loading a table from a text file, use [`LOAD DATA INFILE`](http://dev.mysql.com/doc/en/load-data.html). **This is usually 20 times faster than using [`INSERT`](http://dev.mysql.com/doc/en/insert.html) statements.***" – eggyal Sep 03 '14 at 09:17

0 Answers0