Understanding Linux Memory Commands and MySQL Join Algorithms – An Interview Q&A

Quiz Question

Interviewer: Have you worked with Linux?

Me: Yes.

Interviewer: Which command would you use to view memory usage?

Me: free or top.

Interviewer: What information does free show?

Me: It displays total memory, used memory, free memory, buff/cache, and available memory.

Interviewer: How would you clear the buff/cache?

Me: sync; echo 3 > /proc/sys/vm/drop_caches clears the cache.

Interviewer: Let’s talk about SQL JOIN.

Me: Sure.

Review

In SQL, join combines rows from two or more tables based on a related column.

There are five join types: inner join – inner join left join – left join right join – right join full join – full join cross join – cross join (shown as “full join” in the original images)

Interviewer: How would you optimise a join in a project?

Me: For small data sets, load everything into memory. For large data sets, add indexes, reduce the number of joins (preferably no more than five), and consider using appropriate buffer sizes.

Increasing indexes speeds up join execution; reducing redundant joins also helps.

Interviewer: Why is a join considered performance‑heavy?

Me: Because it requires row‑by‑row comparison, which can be costly.

Buffer Area

Linux treats memory as a cache for disk I/O. The buff/cache field represents memory used for buffering and caching.

Linux can free this cache with sync; echo 3 > /proc/sys/vm/drop_caches, which is why the free command often shows a large buff/cache value.

Reference: https://www.linuxatemyram.com/

Big Premise

In production, data volumes are rarely small; therefore, join performance matters.

MySQL InnoDB stores data in 16 KB pages ( .ibd files). Each join may require reading many pages, causing frequent disk‑head movement.

Thus, reducing I/O by increasing join_buffer_size or using SSDs can improve performance.

Join Algorithms

Nested Loop Join : For each row in the outer table, scan the inner table. This can lead to millions of I/O operations for large tables and is rarely used in modern engines.

Block Nested Loop Join : Reads a block of rows into memory to reduce I/O. MySQL uses this when no suitable index exists.

When indexes are unavailable, InnoDB automatically falls back to the Block Nested Loop algorithm.

Summary

In practice, database performance often outweighs strict normalization. If a join hurts performance, increase join_buffer_size or switch to SSD storage.

References

• "Computer Systems: A Programmer's Perspective" – Chapter 6 (memory hierarchy) • Linux buffer/cache explanation: https://www.linuxatemyram.com/ • MySQL join buffer docs and MariaDB block‑based join algorithms.

-END-