Understanding the Impact of Full Table Scans on MySQL Server and InnoDB Buffer Pool

When scanning a 200 GB InnoDB table on a server with only 100 GB of RAM, many developers worry that a full‑table scan will consume all server memory. In reality MySQL streams query results row by row, using a configurable net_buffer_length (default 16 KB) to hold each row before sending it to the client.

The server does not build a complete result set in memory. It repeatedly reads a row, writes it to the network buffer, and flushes the buffer when full. If the client reads slowly, the socket send buffer can become full, causing the server to pause reading more rows until the client drains the buffer.

MySQL provides two APIs for fetching results:

mysql_store_result – stores the entire result set in client memory (suitable for small result sets).

mysql_use_result – streams rows one by one (recommended for large scans).

When the server’s state shows "Sending to client", it indicates the network stack is blocked; "Sending data" merely reflects that the query is in an execution phase and does not guarantee data is being transmitted.

InnoDB’s internal memory management further protects the server. Data pages are cached in the Buffer Pool (BP), whose size is set by innodb_buffer_pool_size (typically 60‑80 % of physical RAM). The BP uses an LRU list to evict pages, but InnoDB improves the classic LRU with a 5:3 split into New and Old zones, controlled by innodb_old_blocks_time . Pages accessed for less than one second stay in the Old zone, preventing large scans from evicting hot data needed by active workloads.

During a full scan of a historical table, new pages are inserted into the Old zone, leaving the New zone (which serves hot data) untouched. Consequently, the buffer‑pool hit rate for regular queries remains high, and overall I/O pressure is limited.

Key take‑aways:

MySQL streams results, so a full‑table scan will not blow up server memory.

Use mysql_use_result for large result sets; switch to mysql_store_result only when the result set is small.

InnoDB’s enhanced LRU algorithm mitigates the impact of massive scans on the Buffer Pool.

Even with large scans, keep an eye on net_buffer_length and socket buffers to avoid client‑side bottlenecks.

Running heavy scans on a production master during peak traffic is still discouraged due to I/O cost.

Overall, MySQL’s "read‑while‑send" design and InnoDB’s optimized buffer‑pool management ensure that full‑table scans on large tables are memory‑safe, though they remain I/O‑intensive.