MySQL Temporary Tables

Overview

Temporary tables are special tables that are visible only to the current connection and are automatically dropped when the connection closes. They are useful for intermediate results, temporary storage, and data processing.

Temporary Table Characteristics

Connection-Specific: Visible only to creating connection
Automatic Cleanup: Dropped when connection ends
Isolated: Not visible to other connections
Same Name Allowed: Different connections can have same-named temporary tables

Creating Temporary Tables

Basic Syntax

-- Create temporary table
CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(255)
);

-- Create with SELECT
CREATE TEMPORARY TABLE temp_orders AS
SELECT * FROM orders WHERE order_date >= '2024-01-01';

-- Create with LIKE
CREATE TEMPORARY TABLE temp_users_copy LIKE users;

Temporary Table with Data

-- Create and insert in one statement
CREATE TEMPORARY TABLE temp_active_users AS
SELECT * FROM users WHERE status = 'active';

-- Create empty table, then insert
CREATE TEMPORARY TABLE temp_stats (
    user_count INT,
    total_orders INT,
    total_amount DECIMAL(10,2)
);

INSERT INTO temp_stats
SELECT 
    COUNT(*) AS user_count,
    SUM(order_count) AS total_orders,
    SUM(order_total) AS total_amount
FROM user_statistics;

Temporary Table from Another Table

-- Copy structure and data
CREATE TEMPORARY TABLE temp_users_copy AS
SELECT * FROM users;

-- Copy with filter
CREATE TEMPORARY TABLE temp_recent_orders AS
SELECT * FROM orders 
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Copy specific columns
CREATE TEMPORARY TABLE temp_user_emails AS
SELECT id, name, email FROM users;

Temporary Table Operations

INSERT Operations

-- Insert into temporary table
INSERT INTO temp_users (name, email)
VALUES ('John', 'john@example.com'),
       ('Jane', 'jane@example.com');

-- Insert from another table
INSERT INTO temp_orders
SELECT * FROM orders WHERE status = 'completed';

-- Bulk insert
INSERT INTO temp_users
SELECT * FROM users WHERE created_at > '2024-01-01';

UPDATE Operations

-- Update temporary table
UPDATE temp_users 
SET status = 'verified' 
WHERE email LIKE '%@company.com';

-- Update with JOIN
UPDATE temp_users u
JOIN users orig ON u.email = orig.email
SET u.phone = orig.phone;

DELETE Operations

-- Delete from temporary table
DELETE FROM temp_users WHERE id = 1;

-- Delete with condition
DELETE FROM temp_orders 
WHERE order_date < '2024-01-01';

-- Truncate temporary table
TRUNCATE TABLE temp_users;

Temporary Table Queries

Basic Queries

-- Select from temporary table
SELECT * FROM temp_users;

-- Join with regular tables
SELECT u.name, o.order_number
FROM temp_users u
JOIN orders o ON u.id = o.user_id;

-- Aggregate data
SELECT 
    COUNT(*) AS total_users,
    SUM(order_count) AS total_orders
FROM temp_users
JOIN order_counts ON temp_users.id = order_counts.user_id;

Complex Queries

-- Join multiple temporary tables
CREATE TEMPORARY TABLE temp_customers AS
SELECT id, name FROM customers WHERE status = 'active';

CREATE TEMPORARY TABLE temp_orders AS
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id;

-- Analyze customer data
SELECT 
    c.name,
    o.order_count,
    o.total_spent
FROM temp_customers c
LEFT JOIN temp_orders o ON c.id = o.customer_id;

Temporary Table Indexes

Adding Indexes

-- Create indexed temporary table
CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255),
    INDEX idx_temp_email (email)
);

-- Add index to existing temporary table
CREATE INDEX idx_temp_name ON temp_users(name);

-- Add composite index
CREATE INDEX idx_temp_user_date ON temp_orders(user_id, order_date);

Index Performance

-- Indexes improve query performance
CREATE TEMPORARY TABLE temp_large_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    category VARCHAR(50),
    price DECIMAL(10,2),
    INDEX idx_temp_category (category),
    INDEX idx_temp_product (product_id)
);

-- Query uses index
SELECT * FROM temp_large_data 
WHERE category = 'electronics' 
AND price > 100;

Temporary Table Storage

Storage Engine

-- Specify engine for temporary table
CREATE TEMPORARY TABLE temp_data (
    id INT PRIMARY KEY
) ENGINE = InnoDB;

-- Use MEMORY engine for fast access
CREATE TEMPORARY TABLE temp_cache (
    id INT PRIMARY KEY,
    value VARCHAR(100)
) ENGINE = MEMORY;

-- Check engine
SHOW CREATE TABLE temp_data;

In-Memory Temporary Tables

-- MEMORY engine for temporary storage
CREATE TEMPORARY TABLE temp_lookup (
    code VARCHAR(20) PRIMARY KEY,
    description VARCHAR(255),
    value DECIMAL(10,2)
) ENGINE = MEMORY;

-- Fast lookups
SELECT * FROM temp_lookup WHERE code = 'DISCOUNT10';

-- Limitations of MEMORY engine
-- No TEXT/BLOB types
-- Fixed row size
-- Data lost on server restart

Temporary Table Lifecycle

Connection-Specific

-- Connection 1
CREATE TEMPORARY TABLE temp_data (id INT);
INSERT INTO temp_data VALUES (1);
SELECT * FROM temp_data;  -- Returns data

-- Connection 2
SELECT * FROM temp_data;  -- Error: Table doesn't exist
CREATE TEMPORARY TABLE temp_data (id INT);  -- Different table, OK

Automatic Cleanup

-- Temporary tables automatically dropped when:
-- 1. Connection closes normally
-- 2. Connection is killed
-- 3. Explicit DROP TABLE
-- 4. Server restarts

-- Manual drop (optional)
DROP TEMPORARY TABLE temp_data;

-- Check if table is temporary
SELECT TABLE_NAME, TABLE_TYPE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'database_name';
-- TEMPORARY tables show as 'TEMPORARY'

Use Cases

Data Processing

-- Multi-step data transformation
-- Step 1: Extract data
CREATE TEMPORARY TABLE temp_raw_data AS
SELECT * FROM imported_data;

-- Step 2: Clean and transform
CREATE TEMPORARY TABLE temp_clean_data AS
SELECT 
    id,
    UPPER(name) AS name,
    REGEXP_REPLACE(email, '\\s', '') AS email,
    DATE_FORMAT(created_at, '%Y-%m-%d') AS formatted_date
FROM temp_raw_data;

-- Step 3: Load into production table
INSERT INTO users (id, name, email, created_at)
SELECT id, name, email, formatted_date
FROM temp_clean_data;

-- Step 4: Cleanup (automatic on disconnect)

Intermediate Results

-- Complex query broken into steps
-- Step 1: Aggregate orders
CREATE TEMPORARY TABLE temp_order_totals AS
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total) AS order_total
FROM orders
GROUP BY user_id;

-- Step 2: Calculate averages
CREATE TEMPORARY TABLE temp_user_stats AS
SELECT 
    u.id,
    u.name,
    ot.order_count,
    ot.order_total,
    CASE 
        WHEN ot.order_count > 10 THEN 'VIP'
        WHEN ot.order_count > 5 THEN 'Regular'
        ELSE 'New'
    END AS customer_tier
FROM users u
JOIN temp_order_totals ot ON u.id = ot.user_id;

-- Step 3: Final result
SELECT * FROM temp_user_stats;

Data Comparison

-- Compare two data sets
CREATE TEMPORARY TABLE temp_current AS
SELECT id, name, email FROM current_users;

CREATE TEMPORARY TABLE temp_import AS
SELECT id, name, email FROM imported_users;

-- Find new records
SELECT i.* 
FROM temp_import i
LEFT JOIN temp_current c ON i.email = c.email
WHERE c.id IS NULL;

-- Find changed records
SELECT 
    c.id,
    c.name AS current_name,
    i.name AS new_name
FROM temp_current c
JOIN temp_import i ON c.email = i.email
WHERE c.name != i.name;

Session-Specific Data

-- User session data
CREATE TEMPORARY TABLE temp_session (
    user_id INT,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    session_data JSON
);

-- Insert session data
INSERT INTO temp_session (user_id, session_data)
VALUES (1, '{"ip": "192.168.1.1", "browser": "Chrome"}');

-- Update session activity
UPDATE temp_session 
SET last_activity = NOW() 
WHERE user_id = 1;

Performance Optimization

-- Temporary table for caching
CREATE TEMPORARY TABLE temp_product_cache (
    product_id INT PRIMARY KEY,
    name VARCHAR(200),
    price DECIMAL(10,2),
    inventory_count INT
) ENGINE = MEMORY;

-- Populate cache
INSERT INTO temp_product_cache
SELECT * FROM products WHERE active = 1;

-- Fast lookups
SELECT pc.name, pc.price, pc.inventory_count
FROM orders o
JOIN temp_product_cache pc ON o.product_id = pc.product_id;

Temporary Table Limitations

Restrictions

-- Cannot use certain features
-- No foreign keys (in older MySQL versions)
-- Cannot be referenced by views (in older versions)
-- No TRIGGERS on temporary tables

-- Check temporary table limitations
SHOW CREATE TABLE temp_data;

Best Practices

-- 1. Use descriptive names
CREATE TEMPORARY TABLE temp_2024_01_orders AS
SELECT * FROM orders;

-- 2. Clean up explicitly when done
DROP TEMPORARY TABLE IF EXISTS temp_data;

-- 3. Use appropriate engine
-- MEMORY for small, frequently accessed data
-- InnoDB for larger datasets
CREATE TEMPORARY TABLE temp_small (...)
ENGINE = MEMORY;

CREATE TEMPORARY TABLE temp_large (...)
ENGINE = InnoDB;

-- 4. Add indexes for performance
CREATE TEMPORARY TABLE temp_data (
    id INT PRIMARY KEY,
    user_id INT,
    INDEX idx_temp_user (user_id)
);

Temporary Tables in Stored Procedures

Using Temporary Tables

DELIMITER //
CREATE PROCEDURE generate_report(IN user_id INT)
BEGIN
    -- Create temporary table
    CREATE TEMPORARY TABLE temp_report (
        product_name VARCHAR(200),
        quantity INT,
        total DECIMAL(10,2)
    );
    
    -- Populate with data
    INSERT INTO temp_report
    SELECT 
        p.name,
        oi.quantity,
        oi.quantity * p.price AS total
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    WHERE o.user_id = user_id;
    
    -- Return results
    SELECT * FROM temp_report;
    
    -- Temporary table automatically dropped when procedure ends
END //
DELIMITER ;

-- Call procedure
CALL generate_report(1);

Monitoring Temporary Tables

Checking Temporary Tables

-- View temporary tables in current database
SELECT TABLE_NAME, TABLE_TYPE, ENGINE
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'TEMPORARY';

-- View all temporary tables across all databases
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM information_schema.TABLES 
WHERE TABLE_TYPE = 'TEMPORARY';

-- Check temporary table size
SELECT 
    TABLE_NAME,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'TEMPORARY';

Summary

Temporary tables are useful for:

Intermediate Processing: Multi-step data transformations
Session Data: Connection-specific storage
Performance: Caching and optimization
Isolation: Separate from production data
Automatic Cleanup: No manual maintenance needed

Use temporary tables for temporary data processing tasks that don't need to persist beyond the current connection.

Previous: Indexes

Next: Clone Tables

#MySQL Temporary Tables

#Overview

#Temporary Table Characteristics

#Creating Temporary Tables

#Basic Syntax

#Temporary Table with Data

#Temporary Table from Another Table

#Temporary Table Operations

#INSERT Operations

#UPDATE Operations

#DELETE Operations

#Temporary Table Queries

#Basic Queries

#Complex Queries

#Temporary Table Indexes

#Adding Indexes

#Index Performance

#Temporary Table Storage

#Storage Engine

#In-Memory Temporary Tables

#Temporary Table Lifecycle

#Connection-Specific

#Automatic Cleanup

#Use Cases

#Data Processing

#Intermediate Results

#Data Comparison

#Session-Specific Data

#Performance Optimization

#Temporary Table Limitations

#Restrictions

#Best Practices

#Temporary Tables in Stored Procedures

#Using Temporary Tables

#Monitoring Temporary Tables

#Checking Temporary Tables

#Summary