MySQL - Temporary Tables

MySQL: Mastering Temporary Tables

Temporary tables are a special type of table in MySQL that allows you to store an intermediate result set that you can use and manipulate within a single database session. They are incredibly useful for complex reporting, breaking down large calculations, and managing session-specific data without cluttering your main database schema.

What are Temporary Tables?

A temporary table, as its name suggests, exists only temporarily. Its key characteristics are:

• Session-Scoped: It is visible only to the client session that created it. Other database connections cannot see or access it.
• Automatic Deletion: It is automatically dropped when the client session ends (e.g., when you disconnect or your script finishes executing).
• Name Shadowing: You can create a temporary table with the same name as a permanent table. In this case, for the duration of the session, all queries will refer to the temporary table. The permanent table is not affected and becomes accessible again once the temporary table is dropped.

Historical Note: Temporary tables were introduced in MySQL version 3.23. In modern database development, they are a standard and widely used feature.

Creating Temporary Tables

Creating a temporary table is almost identical to creating a permanent one, with the addition of the TEMPORARY keyword.

Syntax

CREATE TEMPORARY TABLE table_name (
    column1_definition,
    column2_definition,
    ...
);

Example: Storing Top Customers

Imagine you have a sales table and you want to run several complex queries on your top-performing customers for the month. Instead of re-calculating the list of top customers for each query, you can store them in a temporary table.

First, let’s assume we have a permanent sales table:

CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    sale_date DATE NOT NULL
);

INSERT INTO sales (customer_id, amount, sale_date) VALUES
(101, 50.00, '2023-10-01'),
(102, 150.00, '2023-10-02'),
(101, 200.00, '2023-10-05'),
(103, 75.00, '2023-10-06'),
(102, 300.00, '2023-10-10');

Now, create a temporary table to hold the total sales for each customer and populate it.

-- 1. Create the temporary table
CREATE TEMPORARY TABLE TopCustomers (
    customer_id INT PRIMARY KEY,
    total_spent DECIMAL(12, 2) NOT NULL
);

-- 2. Populate it from the permanent table
INSERT INTO TopCustomers (customer_id, total_spent)
SELECT customer_id, SUM(amount)
FROM sales
WHERE sale_date >= '2023-10-01'
GROUP BY customer_id
ORDER BY SUM(amount) DESC;

You can now perform any operation on this TopCustomers table as if it were a regular table.

SELECT * FROM TopCustomers;

customer_id	total_spent
102	450.00
101	250.00
103	75.00

Note: Temporary tables do not appear in the output of SHOW TABLES unless you are using a very old version of MySQL. To check if it exists, you can try to SELECT from it or use DESCRIBE TopCustomers;.

Dropping Temporary Tables

While temporary tables are dropped automatically at the end of a session, it is good practice to explicitly drop them when you are finished to free up resources, especially in long-running scripts.

Syntax

Use DROP TEMPORARY TABLE. Including IF EXISTS is recommended to prevent errors if the table doesn’t exist for some reason.

DROP TEMPORARY TABLE IF EXISTS TopCustomers;

After running this command, attempting to query the table will result in an error.

SELECT * FROM TopCustomers;
-- ERROR 1146 (42S02): Table 'your_database.TopCustomers' doesn't exist

Using Temporary Tables in Application Code

When using temporary tables in an application, all related operations (CREATE, INSERT, SELECT, DROP) must occur over the same database connection. If you use a connection pool, you must ensure you acquire a single connection and use it for the entire lifecycle of the temporary table.

Python Example (Demonstrating Session Scope)

import mysql.connector

DB_CONFIG = {
    'user': 'root',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

def temp_table_workflow():
    # All operations must use the same connection object
    with mysql.connector.connect(**DB_CONFIG) as connection:
        print("Connection established.")
        with connection.cursor() as cursor:
            print("Creating temporary table...")
            cursor.execute("""
                CREATE TEMPORARY TABLE SessionData (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    message VARCHAR(255)
                );
            """)

            print("Inserting data into temporary table...")
            cursor.execute("INSERT INTO SessionData (message) VALUES (%s)", ('Hello, from this session!',))
            connection.commit()

            print("Querying temporary table:")
            cursor.execute("SELECT * FROM SessionData;")
            for row in cursor.fetchall():
                print(f"  - Row: {row}")

            print("Explicitly dropping temporary table.")
            cursor.execute("DROP TEMPORARY TABLE IF EXISTS SessionData;")

    print("Connection closed. The temporary table is now gone.")

# --- Run the workflow ---
temp_table_workflow()

Node.js Example (Using a Single Connection)

const mysql = require('mysql2/promise');

async function tempTableWorkflow() {
    let connection;
    try {
        // For temporary tables, get a single connection, not from a pool
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'your_password',
            database: 'your_database'
        });
        console.log('Connection established.');

        console.log('Creating temporary table...');
        await connection.execute(`
            CREATE TEMPORARY TABLE UserActions (
                action_id INT AUTO_INCREMENT PRIMARY KEY,
                action_type VARCHAR(50),
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
            );
        `);

        console.log('Inserting data...');
        await connection.execute(`INSERT INTO UserActions (action_type) VALUES (?), (?)`, ['login', 'view_page']);

        console.log('Querying temporary table:');
        const [rows] = await connection.execute('SELECT * FROM UserActions ORDER BY action_id;');
        console.log(rows);

    } catch (error) {
        console.error('Workflow failed:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('Connection closed, temporary table destroyed.');
        }
    }
}

tempTableWorkflow();

Performance and Storage

MySQL handles the storage engine for temporary tables intelligently:

• In-Memory: By default, temporary tables are created using the InnoDB or MyISAM engine (depending on the default), but MySQL may try to hold them in memory for speed. If the table grows too large (exceeding tmp_table_size and max_heap_table_size limits), it will be automatically converted to an on-disk format.
• ENGINE=MEMORY: You can explicitly create an in-memory temporary table with CREATE TEMPORARY TABLE ... ENGINE=MEMORY;. These are very fast but cannot contain BLOB or TEXT columns.

For most use cases, allowing MySQL to manage the storage is sufficient. The performance gain comes from reducing complex calculations and having a smaller, indexed dataset to work with for subsequent queries.