(See MySQL temp table before temporary table) first, the temporary table is visible only on the current connection, and when the connection is closed, MySQL automatically deletes the table and frees all the space. Therefore, a temporary table with the same name can be created in a different connection, and the operation belongs to the temporary table of this connection. The syntax for creating a temporary table is similar to creating a table syntax, where the addition of the keyword temporary
temporary table is used primarily to make a subset of the large data volumes and improve query efficiency . However, when a statement is written with a problem, it can cause a constant creation of temporary tables, resulting in high disk I/O utilization. I previously wrote a stored procedure with a statement that started with group by. Having, as the following analysis of the statement with explain, the statement will use a temporary table (using temprory), after executing the statement, with show GLOBAL STATUS like ' created_tmp_disk_tables '; and show GLOBAL STATUS like ' created_tmp_tables '; view, find temporary table has been increasing when removing group by. Having again with explain analysis, know, no more using temrory
Reference article: MySQL tuning three steps (slow query, explain profile) MySQL show processlist command detailed MySQL temp table
An SQL statement raises the problem of MySQL to create temporary tables continuously to resolve: Coping to TMP table on desk
