Speed of mysql update / set statement. Multiplying a column

Question

I have 1 million rows in MySql table "temp" and wish to multiply column "t" (int unsigned, indexed) by 1000.

mysql> update temp set t=1000*t;

This process takes 25 seconds. The same statement on not-indexed column takes 10 seconds;

Any ideas how to make this process faster? I have to apply this on over 1e5 tables.

Answer 1

Indexing has nothing to do with the problem here. Think about what you're doing - you're mutating all the rows in your table, so no matter how you select them and if you have an index on t or not, you're still scanning the whole table.

The UPDATE operation == IO is what your bottleneck is. Get faster disks.

If you're using InnoDB, my only advice would be to see if tweaking innodb_flush_log_at_trx_commit and setting it to 2 helps your performance but I doubt it as it's just 1 query. Disabling keys and re-enabling them after UPDATE won't work in InnoDB.

Answer 2

You can turn indexing off and back on after the updates are done

ALTER TABLE tbl_name DISABLE KEYS;
ALTER TABLE tbl_name ENABLE KEYS;

Or if you are using myISAM You can use the delay_key_write flag. You can set it per-table, or globally. You can use the "FLUSH TABLE mytable" command to force mysql to update the on-disk copy of the indexes.

http://dev.mysql.com/doc/mysql/en/create-table.html

http://dev.mysql.com/doc/mysql/en/myisam-start.html

http://dev.mysql.com/doc/mysql/en/flush.html