2

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.

cool_cs
  • 1,661
  • 6
  • 20
  • 26

1 Answers1

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?

Community
  • 1
  • 1
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
  • Is it normal for a table to take 30 minutes load if the table has 30 million rows? – cool_cs Jun 04 '12 at 23:23
  • 1
    @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. – eggyal Jun 05 '12 at 00:22