Temp Tables, Derived Tables und Materialisierung verstehen

Warum temporäre Tabellen eine unterschätzte Waffe sind

Viele Entwickler schreiben immer längere und komplexere SQL-Abfragen mit geschachtelten Subqueries, anstatt den saubereren Weg zu gehen: die Datenbasis in einer MySQL Temp Table zwischenzuspeichern und dann mehrfach darauf zuzugreifen. Das Ergebnis sind Abfragen, die schwer zu verstehen sind und oft auch schlecht performen — weil der MySQL-Optimizer mit tiefverschachtelten Subqueries suboptimale Pläne wählt.

Dieser Artikel erklärt, wie temporäre Tabellen in MySQL wirklich funktionieren: der Unterschied zwischen expliziten CREATE TEMPORARY TABLE-Anweisungen und den internen Temp Tables, die MySQL selbst erzeugt. Dazu kommen Derived Tables, CTE-Materialisierung in MySQL 8 und die kritischen Konfigurationsparameter tmp_table_size und max_heap_table_size.

1. CREATE TEMPORARY TABLE – Session-lokale Tabellen

Eine explizite MySQL Temp Table wird mit CREATE TEMPORARY TABLE erstellt. Sie unterscheidet sich von normalen Tabellen in mehreren wichtigen Punkten: Sie ist nur in der aktuellen Session sichtbar, wird automatisch bei Verbindungsende gelöscht und kann trotzdem Indizes, Constraints und alle Storage-Engine-Features einer normalen Tabelle nutzen.

-- Create a temporary table with structure and data
CREATE TEMPORARY TABLE tmp_monthly_orders (
    order_id       INT          NOT NULL,
    customer_id    INT,
    store_id       SMALLINT,
    grand_total    DECIMAL(12,4),
    status         VARCHAR(32),
    created_at     DATETIME,
    -- Add indexes right at creation time
    PRIMARY KEY    (order_id),
    INDEX          idx_customer (customer_id),
    INDEX          idx_store_status (store_id, status)
) ENGINE=InnoDB;

-- Populate from the main table
INSERT INTO tmp_monthly_orders
SELECT
    entity_id,
    customer_id,
    store_id,
    grand_total,
    status,
    created_at
FROM sales_order
WHERE created_at >= '2025-01-01'
    AND created_at < '2025-02-01';

-- Now run multiple analyses on the same temp table
SELECT status, COUNT(*) AS cnt, SUM(grand_total) AS revenue
FROM tmp_monthly_orders
GROUP BY status;

SELECT store_id, AVG(grand_total) AS avg_order_value
FROM tmp_monthly_orders
GROUP BY store_id;

-- Automatically dropped when session ends
-- Or explicitly:
DROP TEMPORARY TABLE IF EXISTS tmp_monthly_orders;
    

Wichtig: Der Name TEMPORARY verdeckt gleichnamige permanente Tabellen in der aktuellen Session. Wenn eine Temp Table denselben Namen wie eine permanente Tabelle trägt, zeigen alle Abfragen auf die Temp Table — ein häufiger Fallstrick.

2. Derived Tables – Subqueries im FROM-Clause

Eine Derived Table ist eine Subquery, die im FROM-Clause einer äußeren Abfrage als virtuelle Tabelle verwendet wird. MySQL muss entscheiden, ob es die Derived Table "einfalten" (merge) kann — den Plan der Subquery in die äußere Abfrage integriert — oder ob es sie materialisieren muss (als temporäre Tabelle aufbauen).

-- Simple derived table: top customers by total spend
SELECT
    dt.customer_id,
    dt.total_spent,
    c.email
FROM (
    -- This subquery is the derived table
    SELECT
        customer_id,
        ROUND(SUM(grand_total), 2) AS total_spent,
        COUNT(*) AS order_count
    FROM sales_order
    WHERE status = 'complete'
        AND customer_id IS NOT NULL
    GROUP BY customer_id
    HAVING SUM(grand_total) > 500
) AS dt
JOIN customer_entity c ON c.entity_id = dt.customer_id
ORDER BY dt.total_spent DESC
LIMIT 50;

-- EXPLAIN shows select_type = DERIVED for materialized subqueries
EXPLAIN
SELECT * FROM (
    SELECT store_id, SUM(grand_total) AS total
    FROM sales_order
    GROUP BY store_id
) AS store_totals
WHERE total > 10000;

-- Check if derived table was materialized or merged
-- Look for "select_type: DERIVED" in EXPLAIN output
-- With materialization: MySQL creates an internal temp table
-- With merge (MySQL 8 optimizer): subquery is folded into outer query
    

3. Wann MySQL interne Temp Tables erzeugt

Unabhängig von expliziten Temp Tables erzeugt MySQL intern temporäre Tabellen für bestimmte Operationen. Das passiert transparent — der Entwickler sieht es nur in EXPLAIN oder in den Status-Variablen. Interne Temp Tables werden für folgende Fälle erstellt:

• GROUP BY auf nicht-indizierten Spalten: MySQL muss alle Zeilen sortieren/aggregieren, bevor Gruppen gebildet werden können.
• DISTINCT mit JOIN: Duplikateliminierung nach einem Join erfordert oft eine Temp Table.
• ORDER BY mit LIMIT nach einem JOIN: Wenn Sortierung nicht per Index erfolgen kann.
• UNION (nicht UNION ALL): Duplikateliminierung zwischen mehreren Result Sets.
• Derived Tables die nicht eingefaltet werden können: Subqueries mit Aggregaten, DISTINCT, LIMIT oder Windowing-Funktionen.
• Subqueries mit IN/EXISTS gegen große Tabellen: Der Optimizer kann die Subquery materialisieren statt sie als Anti-Join auszuführen.

4. Created_tmp_disk_tables überwachen

Interne Temp Tables starten im RAM (als MEMORY-Tabellen). Überschreiten sie die Größenlimits, werden sie auf die Festplatte ausgelagert — als MyISAM- oder TempTable-Engine-Tabellen. Dieser Übergang kostet erheblich Performance und wird in Created_tmp_disk_tables gezählt.

-- Check current disk temp table statistics
SELECT
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') AS tmp_disk_tables,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Created_tmp_tables')      AS tmp_tables_total,
    ROUND(
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'Created_tmp_disk_tables')
        / NULLIF(
            (SELECT VARIABLE_VALUE FROM performance_schema.global_status
             WHERE VARIABLE_NAME = 'Created_tmp_tables'), 0
        ) * 100, 2
    ) AS disk_tmp_ratio_pct;

-- Target: disk_tmp_ratio_pct should be below 5%
-- If above 10%: increase tmp_table_size and max_heap_table_size

-- Find the sessions currently using temp tables
SELECT
    processlist_id,
    processlist_user,
    processlist_host,
    processlist_db,
    processlist_command,
    processlist_time,
    LEFT(processlist_info, 100) AS query_snippet
FROM performance_schema.threads
WHERE processlist_command != 'Sleep'
    AND processlist_time > 10
ORDER BY processlist_time DESC;
    

5. tmp_table_size und max_heap_table_size

Diese beiden Parameter bestimmen, wie groß eine interne Temp Table im RAM werden darf, bevor MySQL sie auf die Festplatte auslagert. Ein häufiger Fehler: nur tmp_table_size zu erhöhen. Das allein reicht nicht — der Optimizer nimmt den niedrigeren der beiden Werte.

-- Check current settings
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

-- Both must be set to the same value for in-memory temp tables
-- The effective limit = MIN(tmp_table_size, max_heap_table_size)

-- Increase both (runtime, session or global)
SET SESSION tmp_table_size    = 256 * 1024 * 1024;  -- 256 MB
SET SESSION max_heap_table_size = 256 * 1024 * 1024;  -- 256 MB

-- Global setting (survives current session, not persistent)
SET GLOBAL tmp_table_size      = 128 * 1024 * 1024;  -- 128 MB
SET GLOBAL max_heap_table_size = 128 * 1024 * 1024;  -- 128 MB

-- Persistent in my.cnf
-- [mysqld]
-- tmp_table_size      = 128M
-- max_heap_table_size = 128M

-- Note: MySQL 8 uses the TempTable storage engine by default for internal temp tables
-- (instead of MEMORY engine used in MySQL 5.7)
-- TempTable has its own size parameter:
SHOW VARIABLES LIKE 'temptable_max_ram';

-- TempTable can also spill to disk via mmap before using actual disk files
SHOW VARIABLES LIKE 'temptable_use_mmap';
SHOW VARIABLES LIKE 'temptable_max_mmap';
    

6. EXPLAIN: "Using temporary" verstehen

Der Hinweis Using temporary in der Extra-Spalte von EXPLAIN zeigt, dass MySQL eine interne Temp Table erzeugt. Das ist nicht automatisch schlecht — bei GROUP BY und ORDER BY ist es oft unvermeidbar — aber ein Signal zur Prüfung, ob die Abfrage optimiert werden kann.

-- Query that triggers "Using temporary" + "Using filesort"
EXPLAIN
SELECT status, COUNT(*), SUM(grand_total)
FROM sales_order
GROUP BY status
ORDER BY SUM(grand_total) DESC;

-- Output typically shows:
-- Extra: Using temporary; Using filesort
-- This means: MySQL builds a temp table for GROUP BY,
-- then sorts it for ORDER BY

-- Improvement: if status is indexed and there are few distinct values
-- MySQL can sometimes avoid the temp table with an index scan

-- Check if you can rewrite to avoid Using temporary:
-- 1. Add an index on the GROUP BY column
-- 2. Use a covering index that includes all selected columns
-- 3. For aggregations over large tables: accept it, but ensure it stays in RAM

-- EXPLAIN ANALYZE (MySQL 8): shows actual rows processed
EXPLAIN ANALYZE
SELECT customer_id, SUM(grand_total) AS total
FROM sales_order
WHERE status = 'complete'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 10;
    

7. Indexierte Temp Tables für mehrstufige Operationen

Der entscheidende Vorteil expliziter MySQL Temp Tables gegenüber Subqueries: Sie können Indizes erhalten. Eine Subquery wird — wenn materialisiert — ohne Indizes gespeichert. Eine explizite Temp Table mit sorgfältig gewählten Indizes kann die Performance von Multi-Step-Operationen dramatisch verbessern.

-- Scenario: complex aggregation needed multiple times
-- BAD: Repeated subquery execution (subquery runs 3 times)
SELECT s.store_id,
    (SELECT SUM(grand_total) FROM sales_order
     WHERE store_id = s.store_id AND status = 'complete') AS total_revenue,
    (SELECT COUNT(*) FROM sales_order
     WHERE store_id = s.store_id AND status = 'complete') AS order_count,
    (SELECT AVG(grand_total) FROM sales_order
     WHERE store_id = s.store_id AND status = 'complete') AS avg_value
FROM store s;

-- GOOD: Temp table with index, subquery runs once
CREATE TEMPORARY TABLE tmp_store_stats (
    store_id       SMALLINT      NOT NULL,
    total_revenue  DECIMAL(15,4),
    order_count    INT,
    avg_value      DECIMAL(12,4),
    PRIMARY KEY    (store_id)
) ENGINE=InnoDB;

INSERT INTO tmp_store_stats
SELECT
    store_id,
    SUM(grand_total),
    COUNT(*),
    AVG(grand_total)
FROM sales_order
WHERE status = 'complete'
GROUP BY store_id;

-- Now join multiple times at near-zero cost (indexed lookup)
SELECT s.name, ts.total_revenue, ts.order_count, ts.avg_value
FROM store s
JOIN tmp_store_stats ts ON ts.store_id = s.store_id
ORDER BY ts.total_revenue DESC;

DROP TEMPORARY TABLE IF EXISTS tmp_store_stats;
    

8. CTE-Materialisierung in MySQL 8

Common Table Expressions (CTEs) mit WITH ... AS () wurden in MySQL 8 eingeführt. Ein wichtiges Detail ihrer Implementierung: Nicht-rekursive CTEs werden in MySQL 8 materialisiert — einmal ausgeführt und das Ergebnis als interne Temp Table gespeichert. Der Optimizer kann den CTE nicht in die äußere Abfrage einfalten.

-- MySQL 8 CTE: materialized once as internal temp table
WITH complete_orders AS (
    -- This CTE is materialized exactly once
    SELECT entity_id, customer_id, grand_total, store_id
    FROM sales_order
    WHERE status = 'complete'
        AND created_at >= '2025-01-01'
)
-- The outer query can reference it multiple times cheaply:
SELECT
    co.store_id,
    COUNT(*)              AS orders,
    SUM(co.grand_total)   AS revenue
FROM complete_orders co
GROUP BY co.store_id;

-- The CTE as "optimizer fence":
-- MySQL cannot push WHERE conditions from the outer query into the CTE
-- This can be a performance advantage (CTE runs once)
-- or disadvantage (CTE cannot be filtered by outer WHERE)

-- Example of the optimizer fence effect:
WITH all_products AS (
    SELECT entity_id, sku, status FROM catalog_product_entity
    -- WHERE status = 1  ← if you add this here, it's applied during materialization
)
SELECT * FROM all_products WHERE status = 1;
-- MySQL 8 MAY or MAY NOT push the outer WHERE into the CTE
-- Check with EXPLAIN to verify

-- Recursive CTE (not materialized in the same way):
WITH RECURSIVE category_tree AS (
    SELECT entity_id, name, parent_id, 0 AS depth
    FROM catalog_category_entity
    WHERE parent_id = 0
    UNION ALL
    SELECT c.entity_id, c.name, c.parent_id, ct.depth + 1
    FROM catalog_category_entity c
    JOIN category_tree ct ON ct.entity_id = c.parent_id
    WHERE ct.depth < 10  -- prevent infinite recursion
)
SELECT * FROM category_tree ORDER BY depth, name;
    

9. Praxisbeispiel: komplexer Report mit Temp Table

Hier ist ein realistisches Szenario: Ein monatlicher Verkaufs-Report für einen Magento-Shop, der mehrere Aggregationen auf denselben Basisdaten benötigt. Mit einer expliziten MySQL Temp Table läuft die Datenbankabfrage auf einem Server einmal statt dreimal.

-- Step 1: Build the base temp table for the report period
CREATE TEMPORARY TABLE tmp_report_base (
    order_id        INT            NOT NULL,
    customer_id     INT,
    store_id        SMALLINT       NOT NULL,
    status          VARCHAR(32),
    grand_total     DECIMAL(12,4),
    created_at      DATETIME,
    PRIMARY KEY     (order_id),
    INDEX           idx_store     (store_id),
    INDEX           idx_customer  (customer_id),
    INDEX           idx_status    (status)
) ENGINE=InnoDB;

-- Populate with one pass over the orders table
INSERT INTO tmp_report_base
SELECT
    entity_id,
    customer_id,
    store_id,
    status,
    grand_total,
    created_at
FROM sales_order
WHERE created_at >= '2025-01-01'
    AND created_at < '2025-02-01'
    AND status IN ('complete', 'processing');

-- Step 2: Revenue by store (uses idx_store)
SELECT
    store_id,
    COUNT(*)                    AS orders,
    ROUND(SUM(grand_total), 2)  AS revenue,
    ROUND(AVG(grand_total), 2)  AS avg_order_value
FROM tmp_report_base
GROUP BY store_id
ORDER BY revenue DESC;

-- Step 3: Top 10 customers (uses idx_customer)
SELECT
    customer_id,
    COUNT(*)                    AS orders,
    ROUND(SUM(grand_total), 2)  AS total_spent
FROM tmp_report_base
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10;

-- Step 4: Order status distribution (uses idx_status)
SELECT
    status,
    COUNT(*)                              AS orders,
    ROUND(SUM(grand_total), 2)            AS revenue,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct_of_orders
FROM tmp_report_base
GROUP BY status;

-- Cleanup
DROP TEMPORARY TABLE IF EXISTS tmp_report_base;
    

10. Zusammenfassung

MySQL Temp Tables sind mehr als ein Workaround — sie sind ein legitimes Performance-Werkzeug für mehrstufige Operationen. Der kritische Parameter ist das Verhältnis Created_tmp_disk_tables / Created_tmp_tables: bleibt es unter 5%, sind die Temp Tables meist im RAM. Explizite Temp Tables mit Indizes schlagen wiederholte Subqueries auf denselben Daten in der Regel deutlich. CTEs in MySQL 8 sind einmalig materialisierte Temp Tables — der Optimizer Fence kann Vor- und Nachteil zugleich sein.

11. FAQ: MySQL Temp Tables und Materialisierung