Understanding MySQL Temporary Tables, MEM_ROOT Memory Management, and Query Optimization

The article begins with a use‑case where a social platform wants to recommend friends based on users' viewing history, and introduces a simple SQL analysis that groups viewed users by age and gender.

A sample table t_user_view is defined with columns for user IDs, viewed user IDs, gender, age, and timestamps, and the table schema is shown using a CREATE TABLE statement.

An example query selects the count of female users aged 18‑22 viewed by user_id=1 , groups the results by age, and displays the counts, illustrating that age 20 females are most frequently viewed.

The article then runs EXPLAIN on the query, revealing three execution phases: Using where (index lookup), Using temporary (temporary table for grouping), and Using filesort (sorting the groups).

It explains MySQL's temporary tables, distinguishing between memory temporary tables (controlled by tmp_table_size ) and disk temporary tables, and describes how internal memory allocation is handled by the MEM_ROOT structure, which manages blocks via free and used linked lists.

The allocation process is illustrated: initializing MEM_ROOT , requesting blocks from the OS, splitting blocks for query needs, and inserting used blocks into the used list; the release process traverses the used list to reclaim memory back to the free list.

The execution flow of the GROUP BY query is detailed step‑by‑step: creating a temporary table, scanning the secondary index idx_user_viewed_user to locate matching rows, fetching full rows via the clustered index, inserting or updating rows in the temporary table, and finally sorting the results using the sort buffer.

Finally, an optimization suggestion is provided: adding a composite index idx_user_age_sex on (user_id, viewed_user_age, viewed_user_sex) allows MySQL to satisfy the query using the index alone, eliminating the need for a temporary table and filesort, which can be verified with another EXPLAIN . If a temporary table cannot be avoided, the article advises increasing tmp_table_size to keep the temporary table in memory.