MySQL load data infile – acceleration?

by

if you’re using innodb and bulk loading here are a few tips:

sort your csv file into the primary key order of the target table : remember innodb uses
clustered primary keys so it will load faster if it’s sorted !

typical load data infile i use:

truncate <table>;

set autocommit = 0;

load data infile <path> into table <table>...

commit;

other optimisations you can use to boost load times:

set unique_checks = 0;
set foreign_key_checks = 0;
set sql_log_bin=0;

split the csv file into smaller chunks

typical import stats i have observed during bulk loads:

3.5 - 6.5 million rows imported per min
210 - 400 million rows per hour

More Related Contents:

  1. DOs and DONTs for Indexes [closed]
  2. MySQL performance: multiple tables vs. index on single table and partitions
  3. Higher cardinality column first in an index when involving a range?
  4. Does Order of Fields of Multi-Column Index in MySQL Matter
  5. Would it help to add index to BIGINT column in MySQL?
  6. Mysql performance on 6 million row table
  7. With MySQL, how can I generate a column containing the record index in a table?
  8. MYSQL OR vs IN performance
  9. How to Perform an UPSERT so that I can use both new and old values in update part
  10. JDBC batch insert performance
  11. SQL Performance UNION vs OR
  12. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  13. MySQL vs MongoDB 1000 reads
  14. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  15. What is cardinality in MySQL?
  16. MySQL varchar index length
  17. What’s the difference between using INDEX vs KEY in MySQL?
  18. What is better in MYSQL count(*) or count(1)?
  19. Mysql Improve Search Performance with wildcards (%%)
  20. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  21. Mysql permission errors with ‘load data’
  22. How to disable index in innodb
  23. MySQL: Inner join vs Where [duplicate]
  24. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  25. MySQL: Many tables or many databases?
  26. MySQL 5.0 indexes – Unique vs Non Unique
  27. sql group by versus distinct
  28. ALTER TABLE ADD COLUMN takes a long time
  29. MySQL: how to index an “OR” clause
  30. Composite Primary Key performance drawback in MySQL

Leave a Comment