SQL SQL_BIG_RESULT

What is SQL SQL_BIG_RESULT?

SQL_BIG_RESULT hints MySQL to use disk-based temporary tables and sort files for GROUP BY or DISTINCT queries expected to return many rows.

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_BIG_RESULT:

SQL SQL_BIG_RESULT Full Explanation

SQL_BIG_RESULT is an optional SELECT modifier available in MySQL and MariaDB. When placed immediately after SELECT, it tells the optimizer that the result set will be large. For GROUP BY or DISTINCT operations, the server prefers writing the intermediate results to a disk-based temporary table and performing sorting with the MyISAM storage engine. This can be faster than using in-memory temporary tables that later require on-the-fly merging, especially when the grouping columns are not indexed and the output is huge.Important behaviors:- Only meaningful with GROUP BY or DISTINCT. Otherwise it is ignored.- Can improve performance for multi-million-row outputs by reducing memory pressure and avoiding temporary table conversion during execution.- May slow down small or moderately sized queries because disk I/O is higher than memory access.- Mutually exclusive with SQL_SMALL_RESULT. If both are given, the last one wins.- It has no effect in other RDBMS like PostgreSQL or SQL Server; those parsers will raise a syntax error.

SQL SQL_BIG_RESULT Syntax

SELECT SQL_BIG_RESULT
       column_list
FROM   table_name
[WHERE conditions]
[GROUP BY columns]
[ORDER BY columns];

SQL SQL_BIG_RESULT Parameters

Example Queries Using SQL SQL_BIG_RESULT

-- Large aggregation expected to return millions of rows
SELECT SQL_BIG_RESULT country, COUNT(*) AS orders
FROM   orders
GROUP  BY country;

-- Distinct with anticipated huge output
SELECT SQL_BIG_RESULT DISTINCT user_id
FROM   page_views;

Expected Output Using SQL SQL_BIG_RESULT

The optimizer will create a disk-based temporary table (MyISAM in legacy versions, TempTable engine in modern MySQL) to process the GROUP BY or DISTINCT
Final rows are streamed to the client once grouping/sorting completes

Use Cases with SQL SQL_BIG_RESULT

Reporting queries that group hundreds of thousands or millions of rows.
DISTINCT queries pulling vast de-duplicated lists.
Situations where memory size is limited and spilling to disk early is preferable.

Common Mistakes with SQL SQL_BIG_RESULT

Using SQL_BIG_RESULT without GROUP BY or DISTINCT and expecting a speedup.
Assuming it guarantees faster execution for every large query.
Combining with SQL_SMALL_RESULT and being surprised only the latter takes effect.
Expecting it to work in PostgreSQL, SQL Server, or Oracle.

First Introduced In

MySQL 3.23

Frequently Asked Questions

When should I use SQL_BIG_RESULT?

Apply it to GROUP BY or DISTINCT queries that will return many rows so MySQL can write the intermediate results to disk early and avoid memory overuse.

Does SQL_BIG_RESULT guarantee faster queries?

No. It can improve very large aggregations but may hurt smaller ones because disk I/O is slower than memory. Test both approaches.

Is SQL_BIG_RESULT portable across databases?

It is MySQL and MariaDB specific. Other databases will not recognize it and will throw a syntax error.

Can SQL_BIG_RESULT be combined with SQL_SMALL_RESULT?

Both keywords can appear, but the last one in the modifier list overrides the other. Only one behavior is active.