MySQL Temporary Tables

Temporary tables in MySQL are a special feature that allows users to store and manipulate temporary data within a specific session, thereby simplifying complex queries. It is a fast and convenient way to handle data without cluttering the database or creating overly complicated queries. Now, let us explore MySQL temporary tables thoroughly, discussing when they're the optimal choice and how to apply them in practical scenarios.

In MySQL, a temporary table is a type of table that stores a temporary result set, allowing users to reference it multiple times within a single session. They can store any type of data, similar to regular tables. Temp tables are particularly useful for scenarios where complex SELECT queries with JOINs are involved. Instead of dealing with overly intricate or resource-intensive queries, users can retrieve the initial result set, store it in a temporary table, and then process that data with subsequent queries.

Temporary tables were introduced in MySQL 3.23 and quickly proved their efficiency. Before their introduction, MySQL developers had to use subqueries and nested queries to manage temporary data. The implementation of temporary tables simplified this process, enabling faster results with less complexity.

Temp tables use the same storage engines as the regular tables by default. If you need to employ another storage engine for the temp table, you need to specify that at the table creation stage. When a temporary table is created within a session, it is specific to the user who created it, and it remains accessible only to them. Even if multiple users are concurrently working within the same database and create temporary tables with identical names, it won't result in conflicts or failures as each user's session operates independently. However, within a single session, a user cannot create two temporary tables with the same name.

MySQL automatically removes all temporary tables created within a session upon the session's end or when the connection is terminated.

Temporary tables in MySQL can store intermediate data, allowing users to perform standard SQL operations such as SELECT, UPDATE, DELETE, JOIN, and more, just as they would with any regular SQL table.

Here are the most common use cases for MySQL temporary tables:

• Storing intermediate results: For complex queries involving large data sets, temporary tables provide a space to hold intermediate results. This approach not only saves disk space and memory but also ensures that the database remains uncluttered thanks to the automatic deletion of these tables once the session ends.
• Working across multiple databases: Temporary tables optimize the process of fetching and processing data from multiple tables or transferring data from one database to another for further processing. This task would be significantly more challenging without the use of temporary tables.
• Dynamic data processing: Temporary tables offer a solution to avoid tables' locking in scenarios where the data is frequently updated and accessed by multiple users at the same time. They also help reduce the consumption of database resources.
• Creating summary tables: For generating summary reports that aggregate data from various tables, temporary tables offer an efficient solution. They are easier to manage, require less storage than regular tables, and enable faster processing with lower resource usage.

Overall, MySQL temporary tables are a valuable tool for handling complex queries that would be too costly, difficult, or impossible to execute as a single statement.

The syntax used for creating a temporary table in MySQL is the same as the syntax we use when creating a regular table. The only difference is the requirement to specify the TEMPORARY keyword. Without that keyword, you create a regular table.

Let us explore the creation of various temporary tables in MySQL. We use the sakila test database and dbForge Studio for MySQL to illustrate the scenarios.

The simplest way is to use the CREATE TEMPORARY TABLE command. The query must specify the table name, the column names and their types and constraints, and indexes, if necessary.

The basic syntax is as follows:

CREATE TEMPORARY TABLE table_name(
   column1 datatype constraints,
   column1 datatype constraints,
   ...,
   table_constraints
);

Assume we want to create a temporary table in the sakila database to work with some parts of the customers' details. The following query creates a temporary table to store a subset of data from the customer table and inserts that data.

-- 1: Create a temporary table for customers' details
CREATE TEMPORARY TABLE Temp_Customer_Details (
  first_name varchar(45),
  last_name varchar(45),
  email varchar(45),
  registration_date datetime
);

-- 2: Insert data from the existing Customer table into the temporary table
INSERT INTO Temp_Customer_Details (first_name, last_name, email, registration_date)
  SELECT
    first_name,
    last_name,
    email,
    create_date
  FROM customer
  WHERE store_id = 2;

-- 3: View the temp table data
SELECT *
FROM Temp_Customer_Details;

When you need to create a temp table with a structure identical to the existing regular table, a different syntax should be used:

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM original_table
LIMIT 0;

For instance, we need a copy of the category table. The query is as follows:

CREATE TEMPORARY TABLE Temp_Category
SELECT * FROM category
LIMIT 0;

SELECT * FROM Temp_Category;

As you can see, we have an empty table with the same structure.

These are the simplest examples. However, in many cases, we need more advanced tables. Creating advanced MySQL temporary tables often involves more complex structures, such as including various data types, and constraints, and even utilizing them in conjunction with subqueries or JOINs.

In this test scenario, we want to create a temporary table with different data types and some constraints:

CREATE TEMPORARY TABLE IF NOT EXISTS Temp_Customer (
    employee_id INT AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    date_of_joining DATE,
    salary DECIMAL(10, 2),
    PRIMARY KEY(employee_id)
);

SELECT * FROM Temp_Customer;

A newly created Temp_Customer table will store the VARCHAR and DATE data types. We also use a unique constraint on the email column.

As we defined earlier, temporary tables are frequently used to simplify complex JOINs. In our test scenario, we want to retrieve the data from the two tables into one temporary table to check the customers' rentals.

-- 1: Create a temporary table Customers_Rentals
CREATE TEMPORARY TABLE IF NOT EXISTS sakila.Customers_Rentals (
    customer_id INT,
    inventory_id INT,
    rental_date DATETIME,
    return_date DATETIME,
    PRIMARY KEY (customer_id, inventory_id, rental_date)
);

-- 2: Insert data into the temporary table Customers_Rentals with customer information
INSERT INTO sakila.Customers_Rentals (customer_id, inventory_id, rental_date, return_date)
SELECT r.customer_id, r.inventory_id, r.rental_date, r.return_date
FROM sakila.rental r;

-- 3: Join the regular rental table with the temporary table Customers_Rentals to retrieve data about inventories, rental dates, and return dates for unique customers
SELECT cr.customer_id, cr.inventory_id, cr.rental_date, cr.return_date, c.first_name, c.last_name, c.email
FROM sakila.Customers_Rentals cr
JOIN sakila.customer c ON cr.customer_id = c.customer_id;

Temporary tables often store the results of subqueries. We want to retrieve the details about the most active customers who had more than 40 rentals.

CREATE TEMPORARY TABLE Most_Active_Customers
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    COUNT(r.rental_id) AS rental_count
FROM
    customer c
JOIN
    rental r ON c.customer_id = r.customer_id
GROUP BY
    c.customer_id
HAVING
    rental_count > 40;

SELECT * FROM Most_Active_Customers;

These examples prove the versatility of MySQL temporary tables in handling various types of data and scenarios, from basic record-keeping to more complex operations involving JOINs and subqueries.

All temporary tables are removed automatically upon the end of the session. However, there is also an option to drop those tables manually, and some scenarios may suggest such behavior. For instance, when you work with several temp tables within one session, and some of them become not needed anymore, it would be a good practice to remove them manually to not mix the results accidentally.

To remove a temporary table, you can use the DROP TABLE command with the TEMPORARY keyword:

DROP TEMPORARY TABLE table_name;

DROP TEMPORARY TABLE Most_Active_Customers;

SELECT * FROM Most_Active_Customers;

The usage of the TEMPORARY keyword makes sure that the command will remove the temp table only and not the regular table that might have the same name.

Temporary tables in MySQL are an excellent choice when you need to make your operations simpler, avoid overly complicated queries, and complete tasks faster. In this guide, we've delved into the essence of MySQL temporary tables and demonstrated various ways to create them, from the simplest to more advanced forms. However, like any other feature, they require an understanding of their specificities.

With dbForge Studio for MySQL, you can create and manage various types of temporary tables, process the data used in them, and achieve the goals faster using the smart GUI of the Studio.