Optimize MySQL Performance with Session Variables and Temporary Tables

Session-based server variables can also come in handy if you want to avoid nesting queries within each other. Therefore, while the following query will list all flights where the current price is above average:

SELECT FlightID FROM stats
  WHERE CurrPrice >
  (SELECT AVG(CurrPrice) FROM stats);

you can accomplish the same thing by splitting the task into two queries and using a server-side MySQL variable to connect them:

SELECT @avg:=AVG(CurrPrice) FROM stats;
SELECT FlightID FROM stats WHERE CurrPrice > @avg;

These two queries combined will run faster than the first subquery.

MySQL also lets you create temporary tables with the CREATE TEMPORARY TABLE command. These tables are so-called because they remain in existence only for the duration of a single MySQL session and are automatically deleted when the client that instantiates them closes its connection with the MySQL server. These tables come in handy for transient, session-based data or calculations, or for the temporary storage of data. And because they’re session-dependent, two different sessions can use the same table name without conflicting.

Since temporary tables are stored in memory, they are significantly faster than disk-based tables. Consequently, they can be effectively used as intermediate storage areas, to speed up query execution by helping to break up complex queries into simpler components, or as a substitute for subquery and join support.

MySQL’s INSERT…SELECT syntax, together with its IGNORE keyword and its support for temporary tables, provides numerous opportunities for creative rewriting of SELECT queries to have them execute faster. For example, say you have a complex query that involves selecting a set of distinct values from a particular field and the MySQL engine is unable to optimize your query because of its complexity. Creative SQL programmers can improve performance by breaking down the single complex query into numerous simple queries (which lend themselves better to optimization) and then using the INSERT IGNORE…SELECT command to save the results generated to a temporary table, after first creating the temporary table with a UNIQUE key on the appropriate field. The result: a set of distinct values for that field and possibly faster query execution.

Here’s another example: Assume you have a table containing information on a month’s worth of transactions say about 300,000 records. At the end of each day, your application needs to generate a report summarizing that day’s transactions. In such a situation it’s not a good idea, performance-wise, to run SUM() and AVG() functions on the entire set of 300,000 records on a daily basis. A more efficient solution here would be to extract only the transactions for the day into a temporary table using INSERT… SELECT, run summary functions on the temporary table to generate the required reports, and then delete the temporary table. Since the temporary table would contain a much smaller subset of records, performance would be better and the server load would also be lower.

CREATE TEMPORARY TABLE t_stats
  SELECT CurrPrice FROM stats WHERE FlightDate = '2009-04-01';
SELECT @avg:=AVG(CurrPrice) FROM t_stats;
DROP TABLE t_stats