Introduction

ERROR 1031 means MySQL asked a storage engine to perform an operation that engine does not support in the current situation. If the error appears around an ORDER BY query, the real issue is often not the ORDER BY clause itself but the temporary table or storage engine MySQL chose while executing the sort.

Why ORDER BY Can Surface Engine Limitations

MySQL may need an internal temporary table to evaluate ORDER BY, especially when sorting joined results, derived tables, or expressions that cannot be satisfied from an index. The engine used for that temporary work depends on server version and configuration.

According to the MySQL reference manual, internal temporary tables are created for several query patterns that include ORDER BY, and engine behavior differs between versions. Older setups and customized temporary-table settings are more likely to expose engine-specific limits during sorts.

That means the failing table name in the error message is not always your original application table. It can be an internal or temporary structure created during query execution.

How to Diagnose the Real Cause

Start by checking the base table definition and the server settings that influence temporary tables:

1SHOW CREATE TABLE report_rows;
2SHOW TABLE STATUS LIKE 'report_rows';
3SHOW VARIABLES LIKE 'default_tmp_storage_engine';
4SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine';

Then inspect the query plan:

1EXPLAIN
2SELECT customer_id, status, notes
3FROM report_rows
4WHERE created_at >= '2026-01-01'
5ORDER BY notes;

If the plan shows Using temporary or Using filesort, MySQL is doing extra work beyond a simple index scan. That does not guarantee a failure, but it points you to the place where engine limitations often matter.

In many real cases, the fix is to move the user table to InnoDB if it is still using a more limited engine, or to change the query so sorting can use an index instead of a temporary structure.

Common Fixes

The most direct fix for user tables is usually engine conversion:

ALTER TABLE report_rows ENGINE=InnoDB;

InnoDB is the default engine for modern MySQL and supports the broadest set of transactional and query features. If the table is currently MEMORY, ARCHIVE, or another specialized engine, conversion often resolves feature-related failures immediately.

You should also look at the query itself. If you can sort by an indexed column and avoid computed expressions, MySQL may no longer need a problematic temporary table.

1CREATE INDEX idx_report_rows_created_status
2    ON report_rows (created_at, status);
3
4SELECT customer_id, status
5FROM report_rows
6WHERE created_at >= '2026-01-01'
7ORDER BY created_at, status;

This change will not help every workload, but it reduces the cases where MySQL must materialize and sort intermediate results.

Temporary Table Nuance Across Versions

The MySQL documentation notes that internal temporary-table behavior changed significantly in the 8.0 series. Modern releases use the TempTable engine for many in-memory internal temporary tables and InnoDB for on-disk internal temporary tables. On older versions, or on systems with non-default settings, MEMORY or MyISAM may still be involved in ways that expose more limitations.

That version detail matters because the same SQL can fail on one server and succeed on another. When debugging ERROR 1031, always compare:

• MySQL server version
• Table storage engine
• Temporary-table related settings
• Whether the query forces Using temporary

Common Pitfalls

One pitfall is assuming the named table in the error is always the application table you wrote in the query. Internal tables can be involved, especially with sorting and grouping.

Another mistake is trying to "fix" the problem only by increasing buffer sizes. More memory may improve sort performance, but it does not add missing storage engine capabilities.

It is also easy to overlook old or specialized engines that were chosen years ago for narrow performance reasons. For most general workloads, staying on InnoDB avoids many edge-case limitations.

Summary

• 'ERROR 1031 on an ORDER BY query usually points to a storage engine limitation, often involving temporary tables.'
• Use SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN to see which engine and plan MySQL is using.
• Converting the user table to InnoDB is often the cleanest fix when a specialized engine is involved.
• Reducing the need for temporary sorting by using appropriate indexes can also help.
• Compare server versions and temporary-table settings, because internal sort behavior differs across MySQL releases.