Slowcoder Slowcoder - 1 year ago 66
MySQL Question

Mysql query performance with autocommit=0

I am trying to delete more than 2 million records from a table by a mysql query (no joins). The table has around 80 million records.

I used

set autocommit=0;
and it is taking long time to complete. Will this be faster if I run the query with

Answer Source

I'm assuming your table is InnoDB. For those 2 million rows, it needs to keep track of the undo log entries for each modification. This builds up in the memory and will eventually go into disk. That's why it's taking a long time. If you do it in chunks, that'll prevent it from going into disk and for MySQL to keep track of less undo log entries, making things more efficient.

The autocommit happens at the end of your query so it wouldn't do anything.

The best way to figure out what your chunk size should be is by experimenting. Something like

delete from table1 limit 1000;

Then keep doubling it until you come up with the best rows-deleted per time ratio.