Tagged articles
Temporary tables
20 articles · Page 1 of 1

ITPUBMay 6, 2026 · Databases
A sudden disk‑usage alarm reveals MySQL's ibtmp1 file ballooning to 192 GB, prompting a step‑by‑step investigation that explains the file’s purpose, shows how to clear and cap its size, and demonstrates how inefficient SQL queries can trigger such runaway growth.
MySQLPerformance TuningSQL Optimization
0 likes · 8 min read

When a production MySQL query joins ten tables and exceeds 30 seconds, a systematic approach—starting with EXPLAIN analysis, profiling, and configuration checks—combined with layered optimizations such as indexing, join order adjustment, query splitting, temporary tables, materialized views, OLAP engines, and vertical partitioning—can dramatically improve performance.
MySQLOLAPPerformance Tuning
0 likes · 17 min read

This article explains why excessive MySQL joins—often inherited from legacy or Oracle‑migration code—cause performance and maintenance problems, and presents nine concrete optimization techniques including SQL splitting, temporary tables, redundant fields, index tuning, query rewriting, result‑set reduction, configuration tweaks, big‑data tools, and materialized summary tables.
Database PerformanceMySQLSQL
0 likes · 11 min read


ITPUBOct 8, 2024 · Databases
Using many ON COMMIT DELETE ROWS temporary tables in PostgreSQL can cause severe performance degradation and lock contention due to large temp_buffers, unnecessary catalog bloat, and costly truncation loops, especially in older versions or Greenplum forks.
ON COMMITPostgreSQLTemporary tables
0 likes · 10 min read

This article presents a MySQL "table is full" error case, explains how the AI‑powered assistant ChatDBA analyzes possible causes such as insufficient temporary tablespace, heavy temporary table creation, and disk limits, and provides step‑by‑step configuration adjustments to resolve the issue.
AIChatDBAInnoDB
0 likes · 8 min read

This article explains the two kinds of MySQL temporary tables—external and internal—covers the scenarios that trigger their creation, details how MySQL chooses MEMORY, MyISAM or InnoDB storage engines based on system variables and data size, and describes indexing, hash‑field fallbacks, and optimization hints such as SQL_SMALL_RESULT and SQL_BIG_RESULT.
MySQLSQL OptimizationTemporary tables
0 likes · 22 min read


ITPUBMay 5, 2022 · Databases
MySQL creates two kinds of temporary tables—external and internal—based on CREATE TEMPORARY TABLE statements or internal query processing, and uses the MEMORY engine by default unless large objects or the big_tables variable force MyISAM or InnoDB, with detailed guidance on scenarios, storage engine selection, conversion thresholds, indexing, and performance tuning.
MySQLSQLTemporary tables
0 likes · 19 min read

The article explains how Citus extends PostgreSQL to provide distributed tables with full single‑node SQL coverage, enumerates the cross‑node SQL features it cannot handle, and demonstrates concrete workarounds using CTEs and temporary tables with detailed code examples.
CTECitusPostgreSQL
0 likes · 8 min read

The article explains why a simple GROUP BY query on a large user‑view table can become inefficient due to temporary tables, filesort, and memory allocation, demonstrates the execution steps with EXPLAIN, and shows how adding a composite index on user_id, viewed_user_age, and viewed_user_sex eliminates temporary tables and improves performance.
MySQLSQL OptimizationTemporary tables
0 likes · 16 min read

This article explains what MySQL temporary tables and temporary files are, how they are created by users or the optimizer, how to view and clean them up, relevant configuration parameters, monitoring methods, and special cases that may require large storage.
Database PerformanceMySQLSQL Optimization
0 likes · 14 min read

This article explains why MySQL 8.0 appears to lose about 1 GB of disk space when temporary tables are used, detailing the experiment, the role of TempTable, mmap allocation, and how performance_schema and procfs can be used to observe the process.
MySQLTemporary tablesdisk space
0 likes · 6 min read

Through a hands‑on experiment, this article demonstrates how MySQL’s internal temporary tables, governed by tmp_table_size and max_heap_table_size, transition from in‑memory heap storage to on‑disk storage, showing the resulting I/O behavior, disk usage statistics, and the impact of engine configuration.
Disk SpillMySQLTemporary tables
0 likes · 5 min read

This article explains why MySQL GROUP BY can dominate query cost, describes the four execution methods—index‑ordered, external‑sort (filesort), temporary‑table, and index‑skip‑scan for MIN/MAX—shows how filtering interacts with grouping, and offers practical tips such as using FORCE INDEX to obtain the optimal plan.
Database IndexesMySQLTemporary tables
0 likes · 12 min read

This article presents comprehensive best‑practice guidelines for database design, indexing, SQL query tuning, Java data‑handling techniques, and hardware adjustments to dramatically improve database performance and reduce resource consumption.
Java best practicesPerformance TuningSQL indexing
0 likes · 11 min read

In a high‑pressure performance‑optimization project, a team dissected a massive, multi‑with‑clause SQL report into smaller temporary‑table queries, applied dimensional modeling, and achieved a 6‑second runtime reduction while handling complex reporting requirements across regions, contractors, and milestones.
Query RefactoringSQLTemporary tables
0 likes · 10 min read


ITPUBFeb 26, 2017 · Databases
The article examines a high‑volume Oracle query scenario, outlines its inefficiencies when using multiple IN clauses, and compares three optimization approaches—including a single‑SQL join, UNION ALL views, and temporary tables—while providing detailed SQL examples, performance considerations, and practical recommendations.
OracleSQLTemporary tables
0 likes · 10 min read

The article explains MySQL's external and internal temporary tables, describes ten scenarios where internal temporary tables are created—including hints, derived tables, ORDER BY, GROUP BY, IN‑subqueries, UNION and aggregates—and provides example SQL statements with EXPLAIN JSON output to illustrate performance impacts.
MySQLSQL OptimizationTemporary tables
0 likes · 10 min read

This article reviews the most important MySQL 5.7 enhancements—including stronger authentication, password expiration, account locking, simplified SSL, safer initialization, updated SQL_MODE, online DDL improvements, CJK full‑text support, temporary‑table optimizations, spatial data types, and NVM‑specific tweaks—providing practical guidance and configuration examples for DBAs.
5.7Database SecurityMySQL
0 likes · 11 min read

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.
MySQLSQLTemporary tables
0 likes · 2 min read

A MySQL slow‑query caused 100% I/O usage due to a massive temporary table, but by splitting the original SELECT into two statements—first fetching IDs then retrieving full rows—the execution time dropped from several seconds to under a second and I/O fell below 1%.
MySQLSQLTemporary tables
0 likes · 9 min read
