Tagged articles
temporary table
18 articles · Page 1 of 1
An interview scenario reveals that using a massive IN list (hundreds of thousands of IDs) in MySQL quickly exceeds packet limits, forces full‑table scans, and overloads CPU, while batch IN queries cause network storms; the article dissects these pitfalls and presents three progressive strategies—splitting IN, temporary tables with JOIN, and sharding with stream‑merge—to safely query massive ID sets.
IN ClauseMySQLSharding
0 likes · 32 min read


ITPUBDec 20, 2025 · Databases
This article explains the reasons behind MySQL IN‑clause parameter limits, the performance impact of large IN lists, and provides practical optimization techniques such as splitting queries in application code and using temporary tables with EXISTS to improve efficiency.
IN ClauseMySQLSQL Optimization
0 likes · 5 min read

Learn a scalable method to manage the expiration of massive coupon datasets by storing soon-to-expire coupons in a lightweight temporary table and using periodic scheduled tasks to update statuses, delete processed records, and keep the main coupon table performant.
Backend Engineeringcoupon expirationscheduled job
0 likes · 6 min read

This article explains why excessive MySQL joins can cause performance problems and provides a comprehensive set of techniques—including query splitting, temporary tables, denormalization, index tuning, query rewriting, result‑set reduction, configuration tweaks, big‑data tools, and summary tables—to effectively optimize multi‑table join queries.
Database PerformanceDenormalizationMySQL
0 likes · 10 min read

This article explains the most frequent MySQL EXPLAIN EXTRA hints—Using filesort, Using temporary, and Using join buffer—detailing when they appear, how to interpret them, and practical optimization strategies illustrated with concrete SQL examples and execution‑plan outputs.
EXPLAINFilesortMySQL
0 likes · 9 min read

The article explains the strict GTID‑based replication limits on CREATE TABLE … SELECT and temporary‑table statements in MySQL 5.6/5.7, demonstrates practical work‑arounds such as splitting statements or using CREATE TABLE LIKE, and shows how MySQL 8.0’s native DDL atomicity eliminates these constraints.
DDLGTIDMySQL
0 likes · 12 min read

This article investigates a MySQL 5.7.30 replica crash caused by an InnoDB assertion in btr0btr.cc when handling temporary tables, reproduces the issue using the MySQL Test Run framework, analyzes logs and parameters, and proposes configuration and SQL‑optimisation fixes.
Database ConfigurationInnoDBMySQL
0 likes · 15 min read

This article explains how MySQL processes a GROUP BY query on a user view table, detailing the creation and use of memory and disk temporary tables, the MEM_ROOT allocation mechanism, the execution stages revealed by EXPLAIN, and how adding a composite index can eliminate temporary tables and filesort for better performance.
IndexMySQLgroup-by
0 likes · 13 min read

This guide explains what MySQL temporary tables are, how they exist only for the current connection, provides step‑by‑step SQL examples for creating, inserting, querying, and dropping a temporary table, and discusses practical scenarios such as high‑concurrency writes.
MySQLSQLdatabase
0 likes · 3 min read

This article analyzes a slow MySQL aggregation query that joins a large table with a derived table built from many UNION ALL subqueries, explains the execution plan and derived‑table materialization, and presents two optimization approaches—adding taskname indexes and rewriting the query or using a temporary indexed table—to dramatically reduce execution time while preserving result semantics.
Derived TableIndexMySQL
0 likes · 11 min read

This guide explains how to define duplicate rows, use GROUP BY and HAVING to locate them, and apply temporary tables and DELETE statements to remove duplicates while preserving the row with the smallest ID.
DELETEHAVINGSQL
0 likes · 11 min read

This article shows how to identify and remove duplicate rows in a MySQL table by defining duplication, using GROUP BY with HAVING, creating temporary tables with MIN, and applying various techniques—including UNION, nested subqueries, and joins—to handle single‑column and multi‑column duplicate detection.
HAVINGMySQLSQL
0 likes · 11 min read


ITPUBApr 25, 2018 · Databases
Facing a 20‑million‑row master table and millions of rows in up to ten child tables, the article examines the inefficiencies of repeated IN‑clause queries and presents three alternative strategies—including a single‑SQL join, a UNION ALL view, and the use of global temporary tables—detailing their trade‑offs and experimental results.
OracleSQL Optimizationjoin
0 likes · 10 min read


ITPUBMar 13, 2018 · Databases
This article examines a high‑volume Oracle query scenario, explains why using multiple IN clauses on millions of rows leads to costly full‑table scans, and presents three alternative strategies—including a single‑SQL join, UNION ALL view, and temporary‑table approaches—along with practical examples and performance considerations.
Database PerformanceOracleSQL
0 likes · 10 min read

This article examines why a seemingly simple MySQL IN subquery on tens of thousands of rows can take 33 seconds, explains the optimizer’s behavior in MySQL 5.5, and presents practical fixes such as using temporary tables or JOINs to dramatically improve performance.
IN ClauseMySQLjoin
0 likes · 5 min read


ITPUBNov 14, 2016 · Databases
This article examines a high‑volume Oracle query scenario, critiques the current multi‑IN approach, and presents three alternative solutions—including a single‑SQL join, a UNION ALL view, and the use of global temporary tables—detailing their performance implications and practical trade‑offs.
Database PerformanceIndex ScanOracle
0 likes · 10 min read


ITPUBJul 6, 2016 · Databases
A developer encountered severe slowdown when joining a remote server query via a CTE in SSIS, analyzed the underlying CTE and Nested Loops behavior, and resolved the issue by materializing the remote data into a temporary table, achieving a dramatic performance boost.
CTESQL ServerSSIS
0 likes · 7 min read

When MySQL's query optimizer cannot use a suitable index for a GROUP BY, it reads the needed rows, creates a temporary table, and performs sorting, which can degrade performance unless sort_buffer_size is properly configured and large result sets are avoided.
MySQLgroup-bytemporary table
0 likes · 3 min read
