Bug #34334 load data infile is slow for large tables and transactional storage engines
Submitted: 5 Feb 2008 22:08 Modified: 14 Jan 2010 6:12
Reporter: Mark Callaghan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.0.37 OS:Any
Assigned to: CPU Architecture:Any
Tags: data, infile, load, performance

[5 Feb 2008 22:08] Mark Callaghan 
Description:
load data infile uses one transaction for all of the rows loaded from a file. This is very slow for transactional storage engines when the table is large and all of the changes cannot be buffered in memory. This also reduces concurrency for the duration of the load.

How to repeat:
NA

Suggested fix:
Add a 'commit after N rows' option to LOAD DATA INFILE so that a commit is done after every N rows loaded.
[5 Feb 2008 22:23] MySQL Verification Team 
Thank you for the bug report.
[14 Feb 2008 22:42] Mark Callaghan 
InnoDB needs this today. Falcon and Maria will need it when they are in production.
[18 Jun 2008 13:40] Yair dfwerfw 
We encountered same issue, and found out that loading large data in primary key order into Innodb tables improved load time dramatically.
[14 Jan 2010 6:12] Mark Callaghan 
This is a duplicate of http://bugs.mysql.com/bug.php?id=24313