Using Temporary Tables to Speed Up MySQL December 8, 2011Posted by Tournas Dimitrios in Mysql.
Temporary Tables, or temp tables for short, allow you to create a short-term (current session only ) storage place within the database for a set of data that you need to use several times in a single series of operations . Temp tables come into play when it isn’t possible to retrieve all the data that you require using one SELECT statement or when you want to work with subsets of the same , larger resultset over several successive operations . Temp tables are supported in MySQL 3.23 and later . To create temp tables , all you need to do is include the TEMPORARY keyword in a table creation /delete statement . The benefits of using MySQL Temp Tables are :
- They have the same functionality as standard disk-based tables
- You don’t have to worry about removing the table when you’re done because it will be deleted once you log out (CLI) or the session is terminated (web-browser access ) . Attempting to refer to it in a later session will result in an error such as :
Table ‘.temp_table’ doesn’t exist
- You can create them on the fly with schemas that fit ephemeral (short-term) scenarios .
- You can work with a smaller subset of data from a larger disk-based table .
- To aggregate data from different sources , or to replace cursors
Usage notes :
Note the following differences between a permanent table and a temporary table:
- A temporary table supports primary key definitions but does not support foreign key references.
- A temporary table does not support creation of a STAR index because it cannot reference other tables or be referenced by other tables.
- A temporary table does not support segment specification for data or indexes. The segment specifications for data or indexes must each reside in a separate default segment.
- A temporary table does not allow specification of MAXSEGMENTS or MAXROWS PER SEGMENT.
- A temporary table cannot be loaded by the Table Management Utility (TMU).
The following operations cannot be performed on a temporary table :
- ALTER TABLE
- ALTER INDEX
- ALTER SEGMENT
- CREATE VIEW
- CREATE SYNONYM
- CREATE STAR INDEX
- GRANT privilege
- REVOKE privilege
These links may be helpful :
- Temp Tables and Table Variables: When To Use What And Why – Part 1
- Temp Tables and Table Variables: When To Use What And Why – Part 2
- Are SQL Server Temp Tables Really Necessary?