MySQL Inserting large data sets from file with Java
java, mysql
Solution
Tips for fast insertion:
- Use the LOAD DATA INFILE syntax to let MySQL parse it and insert it, even if you have to mangle it and feed it after the manipulation.
Use this insert syntax:
insert into table (col1, col2) values (val1, val2), (val3, val4), ...
Remove all keys/indexes prior to insertion.
- Do it in the fastest machine you've got (IO-wise mainly, but RAM and CPU also matter). Both the DB server, but also the inserting client, remember you'll be paying twice the IO price (once reading, the second inserting)
Problem
I need to insert about 1.8 million rows from a CSV file into a MySQL database. (only one table) Currently using Java to parse through the file and insert each line. As you can imagine this takes quite a few hours to run. (10 roughtly) The reason I'm not piping it straight in from the file into the db, is the data has to be manipulated before it adds it to the database. This process needs to be run by an IT manager in there. So I've set it up as a nice batch file for them to run after they drop the new csv file into the right location. So, I need to make this work nicely by droping the file into a certain location and running a batch file. (Windows enviroment) My question is, what way would be the fastest way to insert this much data; large inserts, from a temp parsed file or one insert at a time? some other idea possibly? The second question is, how can I optimize my MySQL installation to allow very quick inserts. (there will be a point where a large select of all the data is required as well) Note: the table will be eventually droped and the whole process run again at a later date. Some clarification: currently using ...opencsv.CSVReader to parse the file then doing an insert on each line. I'm concating some columns though and ignoring others. More clarification: Local DB MyISAM table