Understanding Linux Memory Commands and SQL Join Optimization in Technical Interviews

Linux Memory Inspection ("Free" Question)

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

Candidate: free or top.

The free output shows several fields: total – total memory used – memory in use free – idle memory buff/cache – memory used for buffers and cache available – memory readily available for new processes

To clear the buffer/cache, the command is: sync; echo 3 > /proc/sys/vm/drop_caches This frees the buff/cache area, giving more usable memory.

SQL Join Overview

Interviewer: Explain your understanding of JOIN in SQL.

Candidate: JOIN combines rows from two tables based on a condition.

Common join types: INNER JOIN – returns rows with matching keys in both tables. LEFT JOIN – returns all rows from the left table and matching rows from the right. RIGHT JOIN – returns all rows from the right table and matching rows from the left. FULL JOIN – returns rows when there is a match in either table.

Performance Optimization for Joins

For small data sets, loading everything into memory is sufficient. For large data sets:

Create appropriate indexes to speed up join lookups.

Reduce the number of joins; avoid more than five tables in a single query.

Increase join_buffer_size to enlarge the in‑memory buffer used during join processing.

InnoDB Storage Details

InnoDB stores data in 16 KB pages. Each table has a dedicated .ibd file. When a join is executed, the engine may need to read many pages, causing frequent disk‑head movement, which degrades performance.

Buffers such as join_buffer hold rows from the inner table during the join. The size of join_buffer_size directly influences join speed.

Join Algorithms

Nested Loop Join : For each row in the outer table, the engine scans the entire inner table, leading to outer_rows × inner_rows reads. This is rarely used for large tables.

Block Nested Loop Join : Reads a block of rows from the outer table into memory, then scans the inner table, reducing I/O. MySQL InnoDB falls back to this algorithm when no suitable index exists.

Example tables t_a and t_b illustrate the algorithm flow.

Summary

In interviews, emphasize that joins can be performance‑intensive, especially on large data sets. Recommend tuning join_buffer_size, adding indexes, and using SSDs to mitigate I/O bottlenecks.