In MySQL 5.6, using InnoDB, I have a rather large table, call it A; (about 4 million rows) in which I need to update a field. The condition regarding what to update is held in another table, B; and the relation between the two is also held in another table, C; so I created my update statement with two joins, like so :
UPDATE A a JOIN (B b, C c) ON (conditions) SET a.myField = 'myValue'
Now I tested this statement on a dump of my target database, on a equivalent machine. Because I needed this dump for other purposes, I tested the thing using a transaction in order to rollback afterwards :
START TRANSACTION;
UPDATE [...]
ROLLBACK;
Which gave me pretty good performance (I think) for my update, about 17 seconds and 8 for the rollback.
My question is this: If I were to run this statement as is, not in a transaction, on my target database, would I obtain the same performance ? In other words, does using explicit transactions affect performance, and if yes, how ?
I tried to look the documentation on this but it is rather sparse on this topic.
SHOW CREATE TABLE {tablename}
andEXPLAIN SELECT ...
(a select query that looks like theUPDATE
query you have without changing anything. Have you increased the innodb_buffer_pool_size and innodb_log_file_size from the defaults?