Fastest way to do bulk import in MYSQL

Question

I have got a question about the fastest way to copy a large number of records (around 10 million) from 1 table to another in MySQL.

The target table has 1 primary key, 4 indexes and 3 foreign key constraints. The source table has the same primary key, 3 equal indexes, no foreign keys. Before copying I can truncate the target table, so it's not necessary to any duplicate key check (source table has the same primary key after all).

On my local development machine I have experimented with insert into, insert ignore into, insert into ... on duplicate key update, replace into. The fastest result so far has been around 75 minutes (equal results for replace into and insert ignore into). Is there a faster way to do this?

Answer 1

(Seems like I'm able to post detailed answer here today... let me know if this has helped you) Here is a test output for you...

mysql> truncate table t1;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t1 select * from t;
Query OK, 3842688 rows affected (36.19 sec)
Records: 3842688  Duplicates: 0  Warnings: 0

mysql>
mysql> \! rm /tmp/t.txt
mysql> select *  from t into outfile '/tmp/t.txt';
Query OK, 3842688 rows affected (7.24 sec)

mysql> load data infile '/tmp/t.txt' into table t1;
Query OK, 3842688 rows affected (25.20 sec)
Records: 3842688  Deleted: 0  Skipped: 0  Warnings: 0

With larger records and data-size you might see increased margin of difference.

If those are MyISAM tables then you might want to review following notes from documentation:

To ignore foreign key constraints during the load operation, issue a SET foreign_key_checks = 0 statement before executing LOAD DATA.

If you use LOAD DATA INFILE on an empty MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE). Normally, this makes LOAD DATA INFILE much faster when you have many indexes. In some extreme cases, you can create the indexes even faster by turning them off with ALTER TABLE ... DISABLE KEYS before loading the file into the table and using ALTER TABLE ... ENABLE KEYS to re-create the indexes after loading the file.

Also loading into/from memory tables is much faster but based on size you've mentioned I suspect we have sufficient memory (you might see table-full errors and issues due to that...)!

Anyways, try following commands and share the speed results:

select * from t into outfile '/tmp/t.txt';

load data infile '/tmp/t.txt' into table t1;

(I guess <60 mins but no magic...)

Answer 2

I have a database where we have the need to import record sets of millions of records daily and on demand. The fastest method we have found so far is the LOAD DATA INFILE method. This is into a table with multiple indexes including a primary key and a multi-column unique key. If one SELECTs the data from the source table with an appropriate ORDER BY clause, you add a little time to the extract, but the right before the LOAD, you can drop the indexes, do the LOAD, then re-add the indexes. Because the source data was already ordered, the index creation time is greatly reduced, and it saves the time of index scans for every record inserted during the LOAD. Overall time to load is reduced, though it is disconcerting to sit there and watch "nothing" happen as the index builds. Took us from 100+ records a sec to over 2000 records a second.

Answer 3

Have you considered using either mydumper or myloader? In tests that they have ran, it appears to have a significant effect on processing time. You can find more information here (https://www.percona.com/).

Or is this a giant CSV type file you are trying to import?