MySQL - Does using an explicit transaction affect query performance? 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.
1: Using autocommit to automatically commit each transaction:

SET autocommit = ON;
UPDATE ...


2: Explicitly turning a single statement into a transaction.  This is 'identical' to #1.

BEGIN;     (aka START TRANSACTION;)
UPDATE ...;
COMMIT;


3: Multiple statements in a transaction:

BEGIN;
UPDATE ...;
UPDATE ...;
COMMIT;


4: Don't use this; some day you will forget to COMMIT:

SET autocommit = OFF;
UPDATE ...;
...
eventually: COMMIT;


5: Not advised.  It can lead to slowdowns due to overflowing the "log":

BEGIN;
a million statements
COMMIT;


Notes:


Each SQL has some small overhead (network transmission; parsing; etc)
Each transaction as a noticeable overhead.  In particular there is disk I/O to make it crash-safe.  Hence #3 is faster than two copies of #1 or #2.
See innodb_flush_log_at_trx_commit for a tradeoff between integrity and speed.


Advice:


First priority: Do what the application needs -- this is, put the appropriate number of statements in each transaction.
Lower priority: Throw together multiple statements when suitable, thereby amortizing the transaction overhead.
In the case of INSERT, it is even better to have a single INSERT statement with multiple rows.  Davit's Comment show a 9x speedup by putting multiple INSERTs in a single transaction.  That is similar to the effect of the single batch insert.
Use ROLLBACK only when appropriate.  It is costly.  This is because InnoDB optimizes for COMMIT and has to do extra effort to undo.
For performance improvements, this whole discussion is pretty far down the checklist.  More important things involve indexing and query formulation.
Multi-thousand-row DELETE and UPDATE have a lot of overhead and stress on the log.  It is better to break them up.