Do MySQL transactional queries improve performance for multiple SELECT statements?

Question

I want to get data from multiple database tables and want to use select queries on more than five tables, using transactional queries to help in performance improvement:

try {
    // First of all, let's begin a transaction
    $this->db->beginTransaction();

    // Multiple select queries 

    $this->db->commit();
    return $data;
} catch (Exception $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $this->db->rollback();
}

Answer 1

Transactions can give big boost for INSERT/UPDATE/DELETE queries (provided that you use transactional engine like InnoDB), but on SELECT only queries, using transactions are NOT likely to improve performance (but may be still needed by your application logic to ensure consistency).

Main reason for speed difference: if you do not use transactions, server must flush every single update to disk as soon as possible, to make sure that data will survive should server crash or lose power. With transactions, server only needs to guarantee that all updates batched in one transactions are committed to disk, or the whole thing is rolled back. In other words, server is free to group many writes into few disk operations, and only needs to commit transaction when it is absolutely sure that data is safe. Performance difference could be easily one or two orders of magnitude.