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

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.

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.