# Load large data from file into innodb table **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [December 18, 2008, 11:04am UTC](https://forums.percona.com/t/load-large-data-from-file-into-innodb-table/1039 "2008-12-18T11:04:02Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![aba1979](https://avatars.discourse-cdn.com/v4/letter/a/bcef8e/32.png) [@aba1979](https://forums.percona.com/u/aba1979)\ **Post date:** [December 18, 2008, 11:04am UTC](https://forums.percona.com/t/load-large-data-from-file-into-innodb-table/1039/1 "2008-12-18T11:04:02Z")
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 ---
**Author:** ![drjay](https://avatars.discourse-cdn.com/v4/letter/d/5e9695/32.png) [@drjay](https://forums.percona.com/u/drjay)\ **Post date:** [December 22, 2008, 1:44pm UTC](https://forums.percona.com/t/load-large-data-from-file-into-innodb-table/1039/2 "2008-12-22T13:44:28Z")
Yeah, I had the same problem but with about a billion rows. You can imagine my frustration waiting for it! I found out you can make it go hundreds of times faster by splitting the file up. Go here: [http://www.fxfisherman.com/forums/forex-metatrader/tools-uti](http://www.fxfisherman.com/forums/forex-metatrader/tools-uti) lities/75-csv-splitter-divide-large-csv-files.html and about half way down is a program to split a csv file up. I found splitting it into groups of 500,000 has the best trade of size and performance. I then used Navicat to do a batch import - opening all of the files at once and importing them all to the same table. ---
**Author:** ![Tgratle](https://avatars.discourse-cdn.com/v4/letter/t/ebca7d/32.png) [@Tgratle](https://forums.percona.com/u/Tgratle)\ **Post date:** [July 1, 2009, 2:57am UTC](https://forums.percona.com/t/load-large-data-from-file-into-innodb-table/1039/3 "2009-07-01T02:57:35Z")
Hello, What I could advise is to try an ETL tool. It is one of the best way to load data into Innodb. Talend Open Studio is an open source ETL tool for data integration and migration experts. It’s easy to learn for a non-technical user. What distinguishes Talend, when it comes to business users, is the tMap component. It allows the user to get a graphical and functional view of integration processes. For more information: [URL][http://www.talend.com/[/URL]](http://www.talend.com/%5B/URL%5D) ---
**Author:** ![f00bar](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/f00bar/32/826_2.png) [@f00bar](https://forums.percona.com/u/f00bar)\ **Post date:** [March 14, 2010, 4:10pm UTC](https://forums.percona.com/t/load-large-data-from-file-into-innodb-table/1039/4 "2010-03-14T16:10:56Z")
engine=innodb; one CSV datafile 100 million rows sorted in primary key order (order is important to improve bulk loading times – remember innodb clustered primary keys) truncate ; set autocommit = 0; load data infile into table … commit; runtime 15 mins depending on your hardware and mysql config. typical import stats i have observed during bulk loads: 3.5 - 6.5 million rows imported per min 210 - 400 million rows per hour other optimisations as mentioned above if applicable: unqiue\_checks = 0; foreign\_key\_checks = 0; split the file into chunks ---
**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\ **Post date:** [March 15, 2010, 9:32am UTC](https://forums.percona.com/t/load-large-data-from-file-into-innodb-table/1039/5 "2010-03-15T09:32:27Z")
See also [http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-l](http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-l) arge-files-safely-into-innodb-with-load-data-infile/