SQL Reference - SQL_BIG

SQL_BIG_RESULT

SQL_BIG_RESULT is a modifier used in SQL that influences the way the query operates. When this modifier is present in a query, it instructs MySQL to use a temporary table to hold the result, even if the query might not normally require one. Importantly, this temporary table will use the MyISAM storage engine. This is most beneficial for large result sets, and can help optimize performance by minimizing the usage of resources in specific scenarios.

Example

SELECT SQL_BIG_RESULT product_id, SUM(price) as total_price
FROM product_sales
GROUP BY product_id;

Output

+------------+--------------+
| product_id | total_price  |
+------------+--------------+
|          1 | 5000         |
|          2 | 3000         |
|          3 | 7000         |
+------------+--------------+

Explanation

The given example uses SQL_BIG_RESULT to modify the SELECT statement. This instruction is used when a large amount of data is expected in the result set. With SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables to store the result set, optimizing for situations where the result data is larger than memory. The example calculates the sum of prices (total_price) for each product_id in the product_sales table.