i want data multiple database tables , want use select queries on more 5 tables, using transactional queries in performance improvement:
try { // first of all, let's begin transaction $this->db->begintransaction(); // multiple select queries $this->db->commit(); return $data; } catch (exception $e) { // exception has been thrown // must rollback transaction $this->db->rollback(); }
transactions can give big boost insert/update/delete queries (provided use transactional engine innodb), on select queries, using transactions not improve performance (but may still needed application logic ensure consistency).
main reason speed difference: if not use transactions, server must flush every single update disk possible, make sure data survive should server crash or lose power. transactions, server needs guarantee updates batched in 1 transactions committed disk, or whole thing rolled back. in other words, server free group many writes few disk operations, , needs commit transaction when absolutely sure data safe. performance difference 1 or 2 orders of magnitude.
