During the MySQL 5.8 Dreaming and brainstorming session at PerconaLive, someone voiced a wish for an “option to make temporary tables created by the optimizer always go to disk”. By default, MySQL creates in-memory temporary tables in the MEMORY engine. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size. If the result set gest bigger than this threshold, MySQL will overflow to-disk based temporary tables. If an user already knows that the results will be larger than the threshold, why bother to create it in the MEMORY engine in the first place?
This request is perfectly reasonable, but there is no need to dream about it, MySQL already has support for it. There are two ways to tell the optimizer that the result set is large, and temporary tables should be created on disk.
- Use system variable: –big-tables
Variable: Global, Session
Default: OFF
Dynamic: YES - Use SQL_BIG_RESULT clause for SELECT
This clause might have a wider impact than just the temporary table, for instance the optimizer might prefer sorting to using a temporary table with a key on the group by elements.
These options are nothing new and have existed in MySQL 5.0 if not earlier. However, we have made some changes to storage options for temporary tables lately too. In 5.7, the default storage engine for on-disk temporary tables is InnoDB instead of MyISAM, but we kept MyISAM as an option, see this blog for more details.