1

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<String> d=data.iterator();

while(d.hasNext()){
    st.clearParameters();
    st.setString(1, (d.next()).toLowerCase());
    st.addBatch();
}
int[]updateCounts=st.executeBatch();
2
  • Ten million rows shouldn't be THAT much. Is the MySQL server tuned at all or is it running on default settings? Jan 31, 2011 at 8:38
  • default setting since it's my local dev machine, so my batch insert way is fine?
    – user12145
    Jan 31, 2011 at 9:04

2 Answers 2

1

You can do a couple of things to speed up the batch insert.

  • Disable any indexes and keys on your table before the upload, this will allow any duplicate rows to be inserted (a bit messy but easy to clean afterwards) but will speed up the insert a big deal

  • Do a batch COMMIT every N rows. If you're using an InnoDB table that works in a fully transactional way just COMMIT every N rows (don't make it too low or too high) so all the transaction is not stored into memory before commiting to disk

Let me know how this tricks work for you!

2
  • how do I disable indexes from my application?
    – user12145
    Jan 31, 2011 at 9:18
  • In order to disable indexes you need to use the ALTER TABLE statement to remove them from the table, then have another ALTER TABLE statement ready for readding them again when you're done. Have a look at dev.mysql.com/doc/refman/5.1/en/alter-table.html
    – lynxman
    Jan 31, 2011 at 9:23
0

I'd suggest inserting into temporary table, w/o indexes at all, then doing insert from it.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .