1

I tried on the mysql console:

source filename

It was taking too long (the total number of rows is around 21 million I think). After investigating a bit I found this, so then I tried:

set autocommit=0;
set unique_checks=0;
set foreign_key_checks=0;
source filename

And it's still taking too long!

Query OK, 11437 rows affected (0.44 sec)
Records: 11437  Duplicates: 0  Warnings: 0

Query OK, 11369 rows affected (0.59 sec)
Records: 11369  Duplicates: 0  Warnings: 0

Query OK, 12282 rows affected (0.44 sec)
Records: 12282  Duplicates: 0  Warnings: 0

Query OK, 12267 rows affected (0.60 sec)
Records: 12267  Duplicates: 0  Warnings: 0

Query OK, 12259 rows affected (4.01 sec)
Records: 12259  Duplicates: 0  Warnings: 0

Query OK, 12285 rows affected (21.36 sec)
Records: 12285  Duplicates: 0  Warnings: 0

Query OK, 12262 rows affected (20.50 sec)
Records: 12262  Duplicates: 0  Warnings: 0

(... after ~20 minutes ...)

Query OK, 11995 rows affected (49.61 sec)
Records: 11995  Duplicates: 0  Warnings: 0

Query OK, 11987 rows affected (36.57 sec)
Records: 11987  Duplicates: 0  Warnings: 0

Query OK, 11996 rows affected (1 min 3.62 sec)
Records: 11996  Duplicates: 0  Warnings: 0

Query OK, 11982 rows affected (31.21 sec)
Records: 11982  Duplicates: 0  Warnings: 0

Query OK, 11984 rows affected (1 min 5.15 sec)
Records: 11984  Duplicates: 0  Warnings: 0

Such a decay in performance with volume shouldn't occur with unique and foreign key checks turned off. So, what am I missing?

  • urr durr You're right, there are indexes. How do I turn them off during importing? –  Jan 01 '15 at 02:00
  • check out here, might provide some more ideas http://stackoverflow.com/questions/9524938/how-to-disable-index-in-innodb – Doon Jan 01 '15 at 03:42
  • @Doon Thanks, for as far as I can see that post doesn't add anything that I haven't tried yet. –  Jan 01 '15 at 12:54
  • Yeah was more for the discussion on indexes to see if you could disable them. Can you use infile? – Doon Jan 01 '15 at 13:52
  • @Doon What do you mean exactly by using infile? I can edit the .sql file, if that's what you're asking. –  Jan 02 '15 at 00:20
  • http://dev.mysql.com/doc/refman/5.0/en/load-data.html Meaning that. Can you use that to do your import. ? – Doon Jan 02 '15 at 00:55

0 Answers0