I need to import 100 million records from a file into a table (schema given below). The table is stored in innodb engine and am using mysql server version 5.0.45.
I tried using ‘LOAD DATA INFILE’ to import data; however, its performance deteriorates as more rows are inserted. Is there any trick that can be used to complete this import in less than a couple of hours. Need a response on an urgent basis. Thanks.
Table schema:
CREATE TABLE t
(
id
int(11) NOT NULL auto_increment,
pid
int(11) NOT NULL,
cname
varchar(255) NOT NULL,
dname
varchar(255) default NULL,
PRIMARY KEY (id
),
UNIQUE KEY index_t_on_pid_and_cname
(pid
,cname
),
KEY index_tags_on_cname
(cname
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8