SQL SQL_SMALL_RESULT

What is SQL_SMALL_RESULT in MySQL?

SQL_SMALL_RESULT is a MySQL SELECT modifier that hints the optimizer a DISTINCT or GROUP BY result set will be small, encouraging in-memory processing for faster execution.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SQL_SMALL_RESULT:

SQL SQL_SMALL_RESULT Full Explanation

SQL_SMALL_RESULT is one of three legacy MySQL SELECT options (along with SQL_BIG_RESULT and SQL_BUFFER_RESULT) that influence how temporary result sets are handled during DISTINCT or GROUP BY operations. When specified, MySQL assumes the final grouped or distinct output will contain only a small number of rows. The optimizer therefore prefers storing intermediate data in memory using temporary tables hashed by key values, instead of writing large sorted files to disk. Because modern MySQL versions automatically estimate result-set sizes, SQL_SMALL_RESULT rarely changes performance and is often unnecessary. However, in edge cases where the optimizer misjudges cardinality—especially on complex views or when statistics are stale—explicitly declaring SQL_SMALL_RESULT can force a faster in-memory strategy. The clause has no effect on the data returned to the client and is ignored when neither DISTINCT nor GROUP BY is present. Other database systems do not support the keyword; using it outside MySQL causes a syntax error.

SQL SQL_SMALL_RESULT Syntax

SELECT SQL_SMALL_RESULT [ALL | DISTINCT]
       select_list
FROM   table_reference
[WHERE condition]
[GROUP BY column_list]
[ORDER BY column_list];

SQL SQL_SMALL_RESULT Parameters

Example Queries Using SQL SQL_SMALL_RESULT

-- Small grouped summary kept in memory
SELECT SQL_SMALL_RESULT city, COUNT(*) AS num_customers
FROM   customers
GROUP  BY city;

-- Distinct IDs expected to be few
SELECT SQL_SMALL_RESULT DISTINCT user_id
FROM   events
WHERE  event_date >= CURDATE() - INTERVAL 1 DAY;

Expected Output Using SQL SQL_SMALL_RESULT

Queries return the same rows as without the hint
Internally, MySQL chooses an in-memory temporary table for the DISTINCT or GROUP BY phase, reducing disk I/O and improving response time when the assumption is correct

Use Cases with SQL SQL_SMALL_RESULT

Aggregations that you know will yield only dozens or hundreds of rows
Reporting queries on highly selective GROUP BY keys
Legacy code bases where the optimizer consistently overestimates result size
Edge cases on older MySQL versions (pre-8.0) where hash-based GROUP BY is faster than sort-merge

Common Mistakes with SQL SQL_SMALL_RESULT

Assuming it reduces the number of rows sent to the client—it only affects execution strategy
Applying it to statements without DISTINCT or GROUP BY, where it is ignored
Expecting benefits on huge result sets; the hint may slow execution if MySQL must spill memory to disk
Using it in non-MySQL databases, which causes a syntax error

First Introduced In

MySQL 3.23

Frequently Asked Questions

What is the practical benefit of SQL_SMALL_RESULT?

It can reduce query time by keeping temporary DISTINCT or GROUP BY results in memory, avoiding disk I/O on genuinely small result sets.

Does SQL_SMALL_RESULT change the rows returned?

No. It affects only the internal execution plan. The client receives identical data with or without the hint.

Can I combine SQL_SMALL_RESULT with SQL_BIG_RESULT?

No. They are mutually exclusive. Specify only one hint per SELECT statement.

Is SQL_SMALL_RESULT supported outside MySQL or MariaDB?

No. Other popular databases do not recognize the keyword and will raise a syntax error.