In this section, we will talk about temporary tables. We will answer the question "Why does MySQL need to create temporary tables with GROUP BY, RANGE, ORDER BY, and other expressions?"
When using the EXPLAIN plan, you will notice that MySQL can create temporary tables when the query uses the following:
- GROUP BY
- RANGE
- ORDER BY
- Other expressions
In the case of the creation of a temporary table, MySQL will always try to create a temporary table in memory at its the first attempt. Then, if MySQL cannot create this temporary table in memory, it will create it on disk, which is not desirable as far as performance is concerned.
MySQL uses the following variables in the configuration:
- tmp_table_size: This determines the maximum size for temporary tables in memory
- max_heap_table_size: This sets the maximum size for MEMORY tables
You can optimize MySQL to...