Temporary Table in MySQL

Introduction

Temporary Tables are a special type of table in MySQL designed to exist only for the duration of the database session or connection.

This feature was first introduced in MySQL version 3.23 as a powerful tool for storing and manipulating temporary data in a database session or connection.

The Temporary Table feature was a big update in MySQL history as before its introduction the developers had to use workarounds like subqueries and nested queries for storing and manipulating temporary data.

What is a Temporary Table?

A temporary table is created and used within a database session or connection to deal (storing, calculating, manipulating) with temporary data without affecting the underlying database.

Temporary tables can store everything that can be stored in a regular table, including text, numbers, dates, and binary data. We can use temporary tables to store the results of calculations, complex queries, and manipulations.

When using temporary tables in MySQL, we don't have to worry about cleaning up the data as the tables are automatically dropped when the session ends. As temporary tables are only visible to the session that created them, they help maintain privacy and prevent conflicts and unauthorized access to the data.

Features of Temporary Tables

Some key features of a temporary table in MySQL are:

1. Temporary tables can store any type of data that a regular table can.
2. We can also use temporary tables for storing intermediate results for complex queries and calculations.
3. Temporary tables are only visible to the session or connection they belong to.
4. The query performance is higher in temporary tables as the amount of data processed is less.
5. We don't have to worry about cleaning up the temporary tables as they are automatically dropped once the session or connection ends.
6. Temporary tables help maintain privacy and avoid conflicts with other users of the database due to their volatile nature.
7. They can also be used to break down complex queries into smaller, more manageable parts to keep things simple.
8. Temporary tables can be used to store frequently accessed data, such as lookup tables, resulting in improved performance by reducing the number of joins and subqueries needed for data retrieval.
9. They can be used to simulate regular tables for testing and experimenting with data without any risk of altering the underlying database.

Syntax for Creating Temporary Table

The syntax for creating temporary tables in MySQL is:

The CREATE TEMPORARY TABLE command is followed by the table definition which includes table name, column names, and their types, constraints, and indexes.

We will learn more about the syntax when we reach the "Creating a Temporary Table in MySQL whose structure is based on a normal table" section below.

MySQL Temporary Table Examples

Let us look at some examples of temporary tables for a better understanding.

Creating a Temporary Table in MySQL and Inserting Data

To create a new Temporary Table and insert data in it, execute the following query:

Query:

Output:

In this method, we are creating a temporary table StudentsInfo by inserting data into it with the help of table definition (CREATE and INSERT INTO) commands and fetching the temporary table's contents using a SELECT statement.

Creating a Temporary Table in MySQL whose Structure is Based on a Normal Table

To create a new Temporary Table whose structure is based on a regular table in the database, execute the following query:

Query:

Output:

In this method, we are creating a temporary table TempEmp by using the structure and data of a pre-existing regular table Employee and inserting the selected data into it using SELECT statements and different clauses like WHERE to filter out specific records.

How to Drop Temporary Table in MySQL

We can drop temporary tables in MySQL using a simple DROP TABLE TableName command, but it is strongly recommended to use DROP TEMPORARY TABLE TableName as this command only works for temporary tables. Using the latter command ensures that only temporary tables will be dropped and nothing is removed from the permanent database.

Let us have a look at the query to drop a temporary table.

Query:

The above query is used to delete temporary tables. We can also use the conventional DROP TABLE TableName method but that runs a risk of deleting regular tables in the database if we are not careful with the table's name.

When to Use MySQL Temporary Tables?

MySQL Temporary Tables are useful when dealing with intermediate data which will not be required after the session ends.

Here are some scenarios where temporary tables can prove to be very useful:

1. Storing intermediate results: Sometimes when working with a large data set, we need to perform complex queries involving multiple statements, commands, and clauses. Temporary Tables are used to store these intermediate results, as storing them on disk or in memory can lead to performance issues. In addition, storing intermediate results in the temporary tables ensures that the table will be dropped at the end of the session and they do not clutter the original database with trivial data.
2. Working with dynamic data: Temporary tables are used for avoiding locking issues when working with dynamic data (data that changes frequently) if multiple users are accessing the same data simultaneously. The locking issues are avoided by reducing the contention for database resources when using temporary tables.
3. Creating summary tables: When we need to generate summary reports based on data from multiple tables, temporary tables can come in handy for creating a summary table that contains the needed aggregated data. Temporary tables are preferred over regular tables for summary reports because of reduced storage requirements, faster processing, reduced contention for resources, and easier maintenance.

The Temporary table in MySQL is a powerful feature that helps us optimize our operations. However, it's important to evaluate the needs of our query and computations and use temporary tables only when necessary, as creating too many temporary tables or using them unnecessarily can lead to high disk Input/Output (I/O) and memory usage that can become a performance bottleneck for our query.