Using Temporary and Memory Tables in MySQL the Right Way

1. Two different concepts that are often conflated

The term temporary table in MySQL discussions frequently refers to two quite different mechanisms. On one hand, the explicit CREATE TEMPORARY TABLE that a developer deliberately creates to hold intermediate results within a session. On the other, the internal temp tables that the query optimizer creates on its own, without any explicit instruction, whenever a query needs to materialize an intermediate result.

This distinction is more than semantic hairsplitting, because both mechanisms have different lifecycles, different configuration knobs, and different typical pitfalls. Anyone lumping both together easily overlooks that a slow query may not use an explicit temporary table at all, but internally creates one that never becomes visible in application code.

2. Explicit CREATE TEMPORARY TABLE: session scope and use cases

A table created with CREATE TEMPORARY TABLE is visible only within the connection that created it, exists independently of any persistent table of the same name, and is automatically dropped when the connection closes. That makes it ideal for multi-step intermediate results in complex reports or for staging data during an import or batch job, without worrying about conflicts with other users' concurrent sessions.

An important pitfall in connection-pooling setups, common in many PHP applications including Magento, is assuming a temporary table persists across multiple requests. Since connection pools rotate and reuse connections, a temporary table created in one request simply no longer exists in the next request over a different physical connection.

-- Intermediate result for a multi-step report
CREATE TEMPORARY TABLE tmp_order_totals AS
SELECT customer_id, SUM(grand_total) AS total_spent
FROM sales_order
WHERE created_at >= CURDATE() - INTERVAL 90 DAY
GROUP BY customer_id;

CREATE INDEX idx_customer ON tmp_order_totals (customer_id);

SELECT c.email, t.total_spent
FROM tmp_order_totals t
JOIN customer_entity c ON c.entity_id = t.customer_id
ORDER BY t.total_spent DESC
LIMIT 50;

3. Internal optimizer temp tables: when MySQL creates them automatically

The optimizer internally creates a temporary table whenever a query needs to materialize an intermediate result that cannot be read directly from an index. Typical triggers are GROUP BY or ORDER BY without a suitable index, DISTINCT queries, UNION constructs that need to remove duplicate rows, and derived tables or subqueries in the FROM clause that the optimizer cannot merge into the outer query.

These internal temp tables are invisible to the developer unless the execution plan is explicitly examined via EXPLAIN, where they show up as Using temporary. Precisely because they are invisible, they are often overlooked when a query is slower than the row count alone would suggest.

4. In-memory vs. on-disk: the TempTable engine and its limits

Since MySQL 8.0.16, the optimizer defaults to the TempTable storage engine for internal temporary tables, which, unlike the older MEMORY engine, can efficiently store variable-length columns such as VARCHAR in memory instead of padding them to a fixed maximum width. As long as the target table stays within the limits configured via tmp_table_size and temptable_max_ram, it remains fully in memory.

Once that limit is exceeded, the TempTable engine falls back to memory-mapped files on disk, before older versions switched entirely to MyISAM or InnoDB as a fallback. This transition from memory to disk happens transparently but costs noticeable performance, since disk access is considerably slower than memory access, especially for queries with many grouping or sorting passes.

5. The MEMORY storage engine for explicit lookup tables

Beyond the optimizer's internal constructs, there is the explicitly usable ENGINE=MEMORY, which lets developers create their own tables kept permanently in RAM. The default index type is a hash index, extremely fast for equality comparisons but unsuitable for range queries such as BETWEEN unless a BTREE index is explicitly requested.

Typical use cases are small, very frequently queried reference tables, such as exchange rate caches or country and status code mappings, where the query frequency justifies keeping them permanently in memory rather than going through the regular buffer pool on every access.

-- Keep a small lookup table permanently in memory
CREATE TABLE currency_rate_cache (
    currency_code CHAR(3) NOT NULL PRIMARY KEY,
    rate_to_base  DECIMAL(12,6) NOT NULL,
    updated_at    DATETIME NOT NULL
) ENGINE=MEMORY;

SELECT rate_to_base FROM currency_rate_cache WHERE currency_code = 'USD';

6. Pitfalls: missing persistence and column type restrictions

The most important pitfall of the MEMORY engine is missing persistence: on a server restart or crash, the entire content of a MEMORY table is irrecoverably lost, only the empty table structure survives. For anything that cannot be repopulated from another source at will, MEMORY is therefore simply unsuitable.

On top of that, classic MEMORY tables do not support BLOB or TEXT columns and, due to the fixed-row format, waste memory unnecessarily on variable-length values, since every row is padded to the maximum defined column width. The per-table limit via max_heap_table_size additionally causes an oversized MEMORY table to be rejected with an error instead of automatically falling back to disk.

7. Size limits and monitoring: spotting hidden disk spills

Whether queries silently fall back to disk-based internal temp tables can be observed through the status variables Created_tmp_tables and Created_tmp_disk_tables. If the latter rises noticeably relative to the total, that points to queries whose intermediate results regularly exceed the configured memory limits or contain column types that prevent in-memory processing.

These metrics should be watched regularly, ideally as a time series in monitoring, rather than only checked once after a performance problem surfaces, because a gradual rise over weeks often points to growing data volumes for which existing indexes or configuration values no longer suffice.

-- Check the share of disk-based temp tables
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

-- Current configuration limits for internal temp tables
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'temptable_max_ram';
SHOW VARIABLES LIKE 'max_heap_table_size';

8. Practical Magento example: report queries, imports, and session handling

In Magento environments, report queries with GROUP BY over large order or inventory tables frequently create internal temp tables, especially when no suitable index covers the grouping columns. Custom import or batch scripts, on the other hand, often benefit from explicit TEMPORARY TABLEs to stage raw data, clean it up, and only afterward move it into target tables via INSERT ... SELECT.

Database-backed sessions, technically possible but not recommended for Magento, should generally be avoided, since session handling through Redis in Hyvä setups sidesteps both the MEMORY engine pitfalls and unnecessary write load on the database.

9. Best practices: when explicit tables make sense, how to reduce optimizer temp tables

Explicit TEMPORARY TABLEs pay off mainly when a complex intermediate result is reused multiple times within the same session, since that avoids repeated, expensive recalculation. For a one-off intermediate step within a single query, a well-written subquery or common table expression is usually enough, without the extra management overhead of a dedicated temporary table.

To avoid internal optimizer temp tables, matching composite indexes that already deliver GROUP BY and ORDER BY columns in sorted order help, along with deliberately dropping unnecessary DISTINCT clauses when uniqueness is already guaranteed by the query logic. Anyone raising tmp_table_size and max_heap_table_size should keep in mind that these limits apply per connection and in part per query, and can quickly add up to significant total memory consumption under many concurrent connections.

10. Summary

11. FAQ: Temporary Tables in MySQL: The Essentials at a Glance