Advanced MySQL 8

Temporary tables

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. ...