TLDR: How MySQL Avoids Performance Hits from Table Scans

Date: 2020-04-26 Source: https://arpitbhayani.me/blogs/midpoint-insertion-caching-strategy

Overview

Explore MySQL InnoDB's buffer pool and its midpoint insertion strategy for efficient cache management and scan resistance. Disk reads are 4x (for SSD) to 80x (for magnetic disk) slower as compared to main memory (RAM) reads and hence it becomes extremely important for a database to utilize main memory as much as it can, and be super-performant while keeping its latencies to a bare minimum.

Key Points

• Disk reads are 4x (for SSD) to 80x (for magnetic disk) slower as compared to main memory (RAM) reads and hence it becomes extremely important for a database to utilize main memory as much as it can, and be super-performant while keeping its latencies to a bare minimum.
• Spatial Locality of Reference: The spatial locality of reference suggests if a row is accessed, there is a high probability that the neighbouring rows will be accessed in the near future.