Should I run multiple SELECTs inside a PDO transaction for performance gains?

Question

Are PDO transcations intended just for UPDATE, INSERT and DELETE or can you achieve performance gains if you use a transaction for mulitple SELECT queries?

Answer 1

If you aren't modifying data, there's not much point in using a transaction, regardless of the impact on performance (which it would actually hurt, not help, since it takes time/resources to manage a transaction).

Of course, there are a few exceptions like if you are trying to set timeouts or lock modes, but in general, explicit transactions are a waste for read-only operations.

Answer 2

You will not gain performance by using transaction.

To gain performance, you can do an EXPLAIN and use that to tune your queries.

Answer 3

From a purist point of view, it would be wrong to use a transaction on SELECT statements 'to improve performance'. Use a transaction to be sure to not get inconsistencies in the data.

From a performance point of view, locking the tables so no other statement can access it in order to speed up your SELECTs might work - but there are probably better solutions than that. For example, use the HIGH_PRIORITY attribute in your SELECT statement:

HIGH_PRIORITY gives the SELECT higher priority than a statement that updates a table. You should use this only for queries that are very fast and must be done at once. A SELECT HIGH_PRIORITY query that is issued while the table is locked for reading runs even if there is an update statement waiting for the table to be free. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).

This way you use the appropriate method to reach the goal, instead of some hack about which other developers (or even a future you) will wonder why it exists.