MySQL

MySQL "The Table Is Full" Error on Temporary Tables During Complex JOIN

"The table is full" for a temporary table means MySQL's internal, in-memory temporary table used to process a complex query (a large join, a sort, a grouping operation) exceeded its configured size limit — the fix generally involves both raising the relevant limits and optimizing the query to need a smaller intermediate result in the first place.

The Problem

A complex query involving joins, grouping, or sorting large intermediate results fails with:

ERROR 1114 (HY000): The table 'tmp_table' is full

Simpler queries against the same tables work fine, pointing specifically at the complexity or intermediate result size of this particular query as the relevant factor.

Why It Happens

MySQL sometimes needs to create internal temporary tables to process certain query types — particularly those involving GROUP BY, ORDER BY on columns not covered by an index, certain complex joins, or UNION operations. These internal temporary tables start in memory (governed by tmp_table_size and max_heap_table_size) and, in many MySQL configurations, should automatically convert to an on-disk table if they exceed the in-memory limit — but the "table is full" error specifically means this fallback didn't happen or wasn't sufficient, often because:

  • The query's intermediate result genuinely exceeds even reasonable configured limits, often due to a join producing a much larger intermediate result than the final output suggests (a join condition that's less selective than intended, temporarily multiplying rows before final filtering)
  • tmp_table_size and max_heap_table_size are set too low for the actual complexity of queries your application runs, using overly conservative defaults inherited from a generic configuration rather than tuned for your actual workload
  • The temporary table contains a column type (like TEXT or BLOB) that prevents it from being created as an in-memory table at all in some MySQL versions/configurations, forcing it to disk immediately — if disk space is also constrained, this can compound into a different but related failure
  • A missing index causing MySQL to need a much larger intermediate result than would be necessary if the query could use an index to narrow the working set earlier in its execution plan

The Fix

First, check your current configured limits:

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

Both settings need to be raised together for a meaningful increase, since MySQL uses the smaller of the two as the effective limit:

SET GLOBAL tmp_table_size = 268435456;    -- 256MB
SET GLOBAL max_heap_table_size = 268435456; -- 256MB

Be deliberate about how high you raise these, since memory-based temporary tables consume real RAM, and a very high limit combined with many concurrent complex queries could contribute to overall memory pressure on the server — raise incrementally and monitor, rather than setting an extremely high value by default.

Investigate whether the query itself can be optimized to need a smaller intermediate result, which is often the more durable fix than continuing to raise memory limits as data grows. Check the query plan for clues:

EXPLAIN SELECT ... /* your complex query */;

Look for Using temporary and Using filesort in the Extra column, which confirm a temporary table and sort are being used — if a missing index is contributing to this, adding an appropriate one can eliminate or significantly shrink the temporary table's need:

CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

Review the join conditions specifically for correctness — an unintentionally loose join condition (matching more rows than logically intended before later filtering narrows the final result) can produce an intermediate result many times larger than the final output actually requires, consuming far more temporary table space than the query's apparent purpose would suggest.

If certain column types (like TEXT/BLOB) in the query's result set are forcing on-disk temporary tables regardless of memory settings, consider whether those specific columns are actually necessary in this particular query — selecting fewer, more targeted columns rather than SELECT * can sometimes avoid triggering this specific constraint entirely.

Still Not Working?

If raising memory limits and optimizing the query both fail to fully resolve the issue for a genuinely necessary, large, complex operation, check whether on-disk temporary table storage is itself constrained — confirm the tmpdir location has adequate free disk space, since even a correctly-falling-back-to-disk temporary table can still hit a "table is full" style error if the disk location itself runs out of space during a very large operation:

SHOW VARIABLES LIKE 'tmpdir';
df -h /path/to/tmpdir

You might also like