1 views collected around this technical thread.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.