Efficiently loading a csv file into a MySQL table

Question

The current way I am loading the file is:

   load data local infile 'file_name' into table tableA  
fields terminated by ',' enclosed by '"' lines terminated by '\n';

Is the optimal way to load in a table in a unix machine. Does it create the optimal table size? I want a table that takes up the smallest space.

Do you have a real-world memory usage problem with millions of rows? If not, you can most likely just go ahead. — Pekka
– Pekka, Commented Jun 4, 2012 at 22:57
Whatever means one uses to get the data into your table, once there it will occupy the same space... — eggyal
– eggyal, Commented Jun 4, 2012 at 23:01
I am worried that my ibdata1 file will increase exponentially — cool_cs
– cool_cs, Commented Jun 4, 2012 at 23:02

Community · Accepted Answer · 2017-05-23 10:34:47Z

2

MyISAM

If the table is MyISAM, you should do the following:

set bulk_insert_buffer_size = 1024 * 1024 * 256;
alter table tableA disable keys;
load data local infile 'file_name' into table tableA  
fields terminated by ',' enclosed by '"' lines terminated by '\n';
alter table tableA enable keys;

InnoDB

If the table is InnoDB, you should do the following:

set bulk_insert_buffer_size = 1024 * 1024 * 256;
load data local infile 'file_name' into table tableA  
fields terminated by ',' enclosed by '"' lines terminated by '\n';

No only will this take up the least space (loading an empty table), but the rows will be buffered in a treelike structure in memory based on the bulk_insert_buffer_size for caching the data quicker during the reload.

If you are worried about ibdata1 exploding, you need to convert all InnoDB tables to use innodb_file_per_table. Please use my InnoDB Cleanup Steps : Howto: Clean a mysql InnoDB storage engine?

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Jun 4, 2012 at 23:02

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cool_cs Over a year ago

Is it normal for a table to take 30 minutes load if the table has 30 million rows?

eggyal Over a year ago

@cool_cs: How big is the CSV? What indexes exist in the table? Are there any before/after INSERT triggers defined? How fast are the local and remote disks? How fast is the network? So many issues at play here.

Collectives™ on Stack Overflow

Efficiently loading a csv file into a MySQL table

1 Answer 1

MyISAM

InnoDB

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

MyISAM

InnoDB

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related