2

I'm running a query which imports 500k records into a table from a .CSV file.

The query is runs for 1h15min, I think is taking way to much time to do this. I was expecting about 10, 20 min.

I've already did a insert query of 35k and it took about 30 seconds. Is there a way of speeding up the process?

The statement I'm calling is the one below:

LOAD DATA LOCAL INFILE 'c:/users/migue/documents/www/mc-cron/excels/BD_YP_vFinal_2.csv'
INTO TABLE leads 
FIELDS TERMINATED BY ';' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';
Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Miguel Pinto
  • 447
  • 1
  • 3
  • 15
  • Hope you've already read this : https://stackoverflow.com/questions/2463602/mysql-load-data-infile-acceleration – Prabhat G Jun 14 '17 at 10:55
  • Also see https://dbahire.com/testing-the-fastest-way-to-import-a-table-into-mysql-and-some-interesting-5-7-performance-results/ – mikep Jun 14 '17 at 11:05

1 Answers1

1

The speed of importing rows into the database mainly depends on the structure of the database, rather than on the imported data, or on the specific command that you use to initiate the import. So, it would be a lot more useful to show us the CREATE TABLE statement that creates your leads table instead of showing us your LOAD DATA command.

A few suggestions for fast inserts:

  1. Try disabling any and all constraints on the table, and re-enabling them afterwards.
    • This includes unique constraints, foreign keys, everything.
  2. Try dropping any triggers before the import, and once the import is done:
    • put the triggers back
    • manually perform the operations that the triggers would have performed if they were active during the import
  3. Try dropping all indexes before the import, (yes, this also includes the primary key index,) and re-creating them once the import is complete.
Mike Nakis
  • 56,297
  • 11
  • 110
  • 142