CODEWITHSUNDEEP
mysql
Shahadat Hossain Khan
Shahadat Hossain Khan

Reputation: 737

How to force MySQL to write temporary tables on disk rather than in memory for specific query?

I know there is a way to force MySQL to write all temporary tables on disk rather than in memory by setting big-tables to 1 ( https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_big_tables ) but I want this behavior for specific query. Weather it return low amount of row or big amount of row. I want MySQL to write it into disk. Is there any workaround to achieve this? More specifically, I want to use that table later as cache table.

Upvotes: 0

Views: 968

Answers (2)

Harly Hallikas
Harly Hallikas

Reputation: 620

You can set per-session system variables. Sample:

mysql> SET SESSION big_tables = On;
Query OK, 0 rows affected (0.00 sec);

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35603

Use the SQL_BIG_RESULT modifier

SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively. For SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if they are created, and prefers sorting to using a temporary table with a key on the GROUP BY elements. For SQL_SMALL_RESULT, MySQL uses in-memory temporary tables to store the resulting table instead of using sorting. This should not normally be needed. https://dev.mysql.com/doc/refman/5.7/en/select.html

Upvotes: 1

Related Questions