Tagged articles
full table scan
35 articles · Page 1 of 1
At 200k QPS, async-profiler flame graphs revealed that synchronous stack-trace capture on every log line and full-table scans of a fee-item cache consumed 25% of CPU; removing the stack trace and indexing the cache by code dropped single-node CPU from 65% to 48% with no RT regression, validated by controlled ablation experiments and production rollout.
CPU optimizationConcurrentHashMapFlame Graph
0 likes · 13 min read


ITPUBOct 1, 2026 · Databases
The article explains why a MySQL SELECT * query scanning 20 million rows won't cause server-side OOM due to its streaming protocol using a 16KB net_buffer, but warns that full table scans can pollute the Buffer Pool, though InnoDB's midpoint insertion strategy and innodb_old_blocks_time (default 1s) protect hot data from eviction.
Buffer PoolInnoDBInterview
0 likes · 13 min read

This article investigates whether SELECT COUNT(*) forces a full table scan, explains MySQL's cost‑based index selection (IO and CPU components), demonstrates the optimizer's choices with real‑world examples, and shows how EXPLAIN and optimizer trace can reveal and improve query performance.
EXPLAINIndex OptimizationMySQL
0 likes · 10 min read

A MySQL query on a varchar column with an unquoted numeric literal triggered implicit type conversion, forcing a full scan of 2 million rows instead of an index lookup; adding quotes cut latency from 19 seconds to 50 ms. The article explains the mechanism, lists four other common index-invalidation patterns, and provides a quick EXPLAIN-based diagnosis checklist.
EXPLAINIndex OptimizationMySQL
0 likes · 7 min read

Even when scanning a 200 GB InnoDB table on a server with only 100 GB of RAM, MySQL does not consume all memory because it streams results using a limited net_buffer, employs socket send buffers, and InnoDB’s optimized LRU algorithm manages the buffer pool to prevent memory blow‑up.
Buffer PoolInnoDBLRU
0 likes · 12 min read

The article explains why scanning a 200 GB InnoDB table on a MySQL server with only 100 GB of memory does not cause an out‑of‑memory crash, detailing MySQL's row‑by‑row net buffer transmission, InnoDB buffer‑pool behavior, and the improved LRU algorithm that limits memory impact.
Buffer PoolInnoDBLRU
0 likes · 10 min read

A real‑world MySQL data‑migration case study shows how using INSERT INTO SELECT without proper indexing caused a full table scan, OOM errors, and data loss, and explains how to avoid the pitfall by adding appropriate indexes and understanding transaction locking.
Database PerformanceINSERT INTO SELECTIndexing
0 likes · 8 min read

A full‑table scan of a 200 GB InnoDB table on a MySQL server with 100 GB RAM does not exhaust server memory because MySQL streams rows to the client, uses a fixed net_buffer, and InnoDB’s optimized LRU algorithm limits buffer‑pool pressure, ensuring stable performance.
Buffer PoolInnoDBLRU
0 likes · 10 min read

Even when scanning tables with tens of millions of rows, MySQL avoids out‑of‑memory crashes by streaming data in small 16 KB net buffers, using socket buffers, and employing an improved LRU algorithm that isolates cold data in the buffer pool’s old generation.
Buffer PoolLRUMySQL
0 likes · 5 min read

A developer’s costly mistake using MySQL’s INSERT INTO SELECT for data migration led to massive OOM errors, a full‑table scan, lost payment records, and ultimately a $10 000 loss and termination, highlighting the need for proper indexing and careful testing.
INSERT INTO SELECTIndexingMySQL
0 likes · 6 min read

This article recounts a real-world MySQL data migration incident where using INSERT INTO SELECT caused out‑of‑memory errors and data loss, analyzes why full table scans and transaction locking led to failures, and offers practical recommendations such as indexing and avoiding bulk inserts for large tables.
Database PerformanceINSERT INTO SELECTdata migration
0 likes · 8 min read

A developer’s costly mistake using MySQL’s INSERT INTO SELECT for large data migration led to OOM, full-table scans, and lost payments, revealing how transaction locks and missing indexes can cause production failures and how to prevent them.
Database PerformanceINSERT INTO SELECTMySQL
0 likes · 6 min read

A real‑world MySQL post‑mortem shows how using INSERT INTO SELECT for large‑scale data migration caused full‑table scans, transaction‑level locking, data loss and a costly dismissal, and explains how proper indexing and query design can prevent such disasters.
INSERT INTO SELECTMySQLdata migration
0 likes · 7 min read

A real‑world MySQL incident shows that using INSERT INTO SELECT without proper indexing can trigger full table scans, lock tables, cause OOM and data loss, while a careful analysis and adding indexes can safely resolve the issue.
INSERT INTO SELECTfull table scan
0 likes · 7 min read

The article analyzes a real‑world MySQL data‑migration failure caused by an INSERT INTO SELECT operation that triggered full‑table scans and row‑level lock contention, leading to massive data loss and highlighting the need for proper indexing and cautious use of this command.
Database LocksINSERT INTO SELECTMySQL
0 likes · 7 min read

A developer used MySQL’s INSERT INTO SELECT to migrate millions of rows, causing full‑table scans, OOM errors and lost payment records, leading to a costly incident; the post analyzes the root causes, explains transaction locking behavior, and offers practical indexing and testing safeguards.
Database MigrationINSERT INTO SELECTMySQL
0 likes · 7 min read

A MySQL data‑migration task using INSERT INTO SELECT triggered a full‑table scan, leading to lock contention, payment‑record loss, and ultimately the developer's dismissal, highlighting the dangers of unindexed bulk operations and the need for proper indexing and testing.
INSERT INTO SELECTMySQLdata migration
0 likes · 7 min read

This article recounts a real‑world MySQL data‑migration failure caused by an unguarded INSERT INTO SELECT that triggered OOM, full‑table scans, and row‑level locking, explains why testing missed the problem, and offers indexing and transaction‑level safeguards to prevent similar incidents.
Database MigrationINSERT INTO SELECTMySQL
0 likes · 6 min read

A real‑world MySQL case study shows how using INSERT INTO SELECT for large data migrations can trigger full‑table scans, lock contention, and massive data loss, emphasizing the need for proper indexing and careful testing before deployment.
INSERT INTO SELECTIndexingMySQL
0 likes · 7 min read

This article analyzes the differences between MySQL's full table scan and full primary‑key scan by examining execution plans, source‑code paths, and runtime behavior, demonstrating that both ultimately read data from the primary‑key index and explaining why the optimizer distinguishes them.
InnoDBMySQLPrimary Key Scan
0 likes · 9 min read

This article breaks down MySQL's query optimizer cost model by detailing how full‑table‑scan cost is computed, covering the underlying formulas, required statistics, memory‑vs‑disk page ratios, and configurable cost constants with concrete code examples.
MySQLcost-modelfull table scan
0 likes · 8 min read

A MySQL 5.6+ optimizer bug causes queries that filter by uid and order by id with a small LIMIT to use a costly full table scan instead of the appropriate idx_uid_stat index, and the article explains the root cause, shows optimizer_trace output, and offers two practical work‑arounds.
MySQLORDER BYOptimizer
0 likes · 8 min read

The article explains why MySQL sometimes chooses a full table scan instead of using an available index when a query includes ORDER BY id ASC LIMIT 1, demonstrates the optimizer's cost calculations with optimizer_trace, and offers two practical work‑arounds to force the optimal index usage.
MySQLfull table scanoptimizer trace
0 likes · 8 min read

Even when scanning a 200 GB InnoDB table on a server with only 100 GB RAM, MySQL streams results using a small net buffer and an optimized InnoDB buffer‑pool LRU, so the server’s memory never blows up, though I/O load remains high.
Buffer PoolInnoDBLRU algorithm
0 likes · 11 min read

This article explains why a MySQL query that scans a 100‑gigabyte table and returns millions of rows does not exhaust server memory, describing the net_buffer mechanism, socket send buffer behavior, InnoDB buffer‑pool management, and the improved LRU algorithm used to keep large scans from degrading overall performance.
Buffer PoolInnoDBLRU
0 likes · 8 min read

Full table scans on large InnoDB tables do not exhaust MySQL server memory because results are streamed in small net buffers, while InnoDB’s optimized LRU algorithm and buffer pool management ensure that massive scans minimally affect buffer pool hit rates and overall system performance.
Buffer PoolInnoDBLRU
0 likes · 11 min read

This article explains how MySQL handles massive full‑table scans without blowing up server memory, detailing the role of net_buffer, socket send buffers, the "edge‑compute‑and‑send" model, and InnoDB's optimized LRU algorithm that protects the buffer pool during large scans.
Buffer PoolInnoDBLRU
0 likes · 11 min read

The article examines a MySQL optimizer bug where a query with a WHERE clause on uid, ORDER BY id ASC, and LIMIT 1 incorrectly triggers a full table scan despite a usable composite index, explains the optimizer’s cost calculations, and offers workarounds such as FORCE INDEX or a harmless arithmetic trick to force index usage.
IndexMySQLOptimizer
0 likes · 8 min read

This article explains how MySQL's cost‑based optimizer decides between using an index and performing a full table scan, demonstrates the behavior with a large test table, shows optimizer trace details, and clarifies why index access can sometimes be more expensive than scanning the whole table.
IndexMySQLcost-based optimizer
0 likes · 8 min read

The article analyzes a MySQL optimizer bug where a query with ORDER BY id ASC LIMIT 1 triggers a full‑table scan despite an applicable idx_uid_stat index, explains the cost‑based decision process, and presents two practical work‑arounds—using FORCE INDEX or a harmless arithmetic expression—to ensure the index is used.
MySQLOptimizerSQL
0 likes · 7 min read

This article recounts a costly MySQL data‑migration failure caused by an unchecked INSERT INTO SELECT that triggered full‑table scans, lock contention and out‑of‑memory errors, and explains how proper indexing and awareness of transaction isolation can prevent such incidents.
INSERT INTO SELECTMySQLdata migration
0 likes · 7 min read

An engineer’s costly mistake using MySQL’s INSERT INTO SELECT for nightly data migration led to out‑of‑memory crashes, full‑table scans, and payment record loss, prompting a deep dive into locking behavior, transaction isolation, and how proper indexing can safely rescue large‑scale inserts.
INSERT INTO SELECTIndexingMySQL
0 likes · 7 min read

This article explains how MySQL processes a full‑table‑scan query, detailing the construction of read‑sets, template creation, cursor positioning, row conversion to MySQL format, where‑clause filtering, and subsequent row iteration, while highlighting the performance impact of the number of selected fields.
Database InternalsMySQLRead Set
0 likes · 11 min read


ITPUBJun 29, 2016 · Databases
The article explains how Oracle 11g may bypass the buffer cache with direct path reads during full table scans, lists common causes such as large sorts, hash joins, and parallel queries, and shows how hidden parameters like _serial_direct_read can be adjusted to improve performance.
10g11gDirect Path Read
0 likes · 4 min read


ITPUBFeb 25, 2014 · Databases
The article explains how costly full‑table scans in large databases, such as a bank's million‑record account table, can be reduced to minutes through effective query‑optimization techniques, and outlines the main strategies, automation possibilities, and future trends in SQL performance.
Query Tuningdatabase optimizationfull table scan
0 likes · 3 min read
