mysql mass insert data
- by user12145
Edit:
I realized that if I construct a large query in memory, the speed has increased almost 10 times of magnitude
"insert ignore into xxx(col1, col2) values('a',1), values('b',1), values('c',1)..."
Edit:
since I have an index on the first column, the insert time creeps up as I insert more. Can I delay the index until the end?
Original:
I'm using the following to batch insert 10 million rows into mysql db(not all at once, since they don't all fit into memory), it's too slow(taking many hours). should I use load file to improve performance? I would have to create a second file to store all the 10 million rows, then load that into db. are there better ways?
PreparedStatement st=con.prepareStatement("insert ignore into xxx (col1, col2) "+
" values (?, 1)");
Iterator d=data.iterator();
while(d.hasNext()){
st.clearParameters();
st.setString(1, (d.next()).toLowerCase());
st.addBatch();
}
int[]updateCounts=st.executeBatch();
