How to Monitor and Optimize MySQL Temporary Tables for Better Performance
This guide explains how to view MySQL temporary table statistics, interpret key metrics such as created_tmp_tables and created_tmp_disk_tables, and adjust tmp_table_size to keep temporary tables in memory, thereby improving query performance and reducing disk I/O.
Temporary tables are intermediate tables created during SQL execution, such as when performing multi‑table joins.
To check the usage status of temporary tables, run: show global status like 'created_tmp%'; The following counters are relevant:
created_tmp_tables : increments each time a temporary table is created.
created_tmp_disk_tables : increments when a temporary table is created on disk.
created_tmp_files : shows the number of temporary files created by the MySQL service.
An ideal configuration keeps the proportion of disk‑based temporary tables low: created_tmp_disk_tables / created_tmp_tables * 100% <= 25% Check the temporary table size settings with: show variables like '%tmp_table_size%'; The default tmp_table_size is 32 MB, meaning only temporary tables smaller than this can reside entirely in memory; larger ones spill to disk.
You can increase this limit, for example: set session tmp_table_size=40000000; Because tmp_table_size has session scope, use the session keyword when setting it.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
Java High-Performance Architecture
Sharing Java development articles and resources, including SSM architecture and the Spring ecosystem (Spring Boot, Spring Cloud, MyBatis, Dubbo, Docker), Zookeeper, Redis, architecture design, microservices, message queues, Git, etc.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.