The speed of the mysql update / set command. Column Multiplication

Question

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

mysql> update temp set t=1000*t;

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

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

Answer 1

Indexing has nothing to do with the problem here. Think about what you are doing - you are 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 are still looking at the whole table.

UPDATE == IO - . .

InnoDB, - , innodb_flush_log_at_trx_commit 2 , 1 . UPDATE InnoDB.

Answer 2

.

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

, myISAM delay_key_write. , . "FLUSH TABLE mytable", mysql .

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