Understanding Linux Buffer Cache and SQL Join Performance

During a mock interview, the candidate is asked about Linux memory inspection using commands like free and top , and the meaning of the fields total, used, free, buff/cache, and available.

The interviewer then introduces a command to clear the buffer cache: sync; echo 3 > /proc/sys/vm/drop_caches , and discusses its safety in production environments.

Next, the conversation shifts to SQL join concepts. The candidate reviews the purpose of joins, lists the common join types (inner, left, right, full) and shows illustrative images for each.

To improve join performance, the candidate mentions indexing, reducing the number of joins, and limiting joins to no more than five per query.

The discussion then covers MySQL buffer variables, showing how to query them with show variables like '%buffer%' and explaining that join_buffer_size influences join execution speed.

Further, the candidate explains that large tables require many file reads, causing frequent disk‑head movements, and that Linux uses the page cache (buff/cache) as a fast disk cache, referencing the principle that each storage layer acts as a cache for the layer below.

Finally, the interview explores join algorithms. Without indexes, MySQL may fall back to a Nested Loop Join, which reads one row at a time and can be extremely slow for large tables. A more efficient alternative is the Block Nested Loop Join, which reads blocks of rows into memory to reduce I/O, and is used by InnoDB when no suitable index exists.

The article concludes with practical advice: increase join_buffer_size , use SSDs, and consider denormalization when joins become a performance bottleneck.

Reference images and links are retained from the original source.