Why MySQL Queries Slow Down and How to Optimize with Indexes

Introduction

After a social platform runs for a while, we want to recommend and pin friends that a user is interested in when they search for friends. We analyze the user's past behavior and recommend friends based on the analysis.

Here we use the simplest SQL analysis: count the gender and age of friends the user has viewed, group by age, and recommend the gender‑and‑age combination with the highest count.

Assume we have a detail table t_user_view with the following structure:

CREATE TABLE `t_user_view` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto‑increment id',
  `user_id` bigint(20) DEFAULT NULL COMMENT 'user id',
  `viewed_user_id` bigint(20) DEFAULT NULL COMMENT 'viewed user id',
  `viewed_user_sex` tinyint(1) DEFAULT NULL COMMENT 'viewed user sex',
  `viewed_user_age` int(5) DEFAULT NULL COMMENT 'viewed user age',
  `create_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `update_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_viewed_user` (`user_id`,`viewed_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We duplicate the gender and age fields in the table to simplify SQL statistics.

For user_id = 1 we group‑count female friends aged 18‑22:

SELECT viewed_user_age AS age, COUNT(*) AS num
FROM t_user_view
WHERE user_id = 1 AND viewed_user_age BETWEEN 18 AND 22 AND viewed_user_sex = 1
GROUP BY viewed_user_age;

The result shows:

Age 18: 2 females

Age 19: 1 female

Age 20: 3 females

Thus user 1 is most interested in 20‑year‑old females and should receive more recommendations of that group.

If the t_user_view table reaches tens of millions of rows, the query efficiency will drop sharply. Why, and how can we optimize it?

Explain

We first run EXPLAIN on the query:

EXPLAIN SELECT viewed_user_age AS age, COUNT(*) AS num
FROM t_user_view
WHERE user_id = 1 AND viewed_user_age BETWEEN 18 AND 22 AND viewed_user_sex = 1
GROUP BY viewed_user_age;

The result contains three Using entries in the Extra column, representing the three execution stages of the GROUP BY statement:

Using where: locate rows via the idx_user_viewed_user index and then back‑track to fetch remaining columns.

Using temporary: store intermediate group and count data in a temporary table.

Using filesort: sort the groups using the sort buffer.

The term “temporary table” refers to the memory or disk area MySQL uses to hold intermediate results.

Temporary Table

MySQL has two kinds of temporary tables:

Memory temporary table

Disk temporary table

Memory Temporary Table

When the amount of grouped data fits into memory (controlled by tmp_table_size), MySQL stores it in a memory temporary table.

Disk Temporary Table

If the grouped data exceeds tmp_table_size, MySQL spills it to a disk temporary table, which is slower due to I/O.

MEM_ROOT Memory Allocation

MySQL uses a structure called MEM_ROOT to manage memory for temporary tables. It consists of two singly‑linked lists:

free : blocks of unused memory (each block has left, size, and next).

used : blocks that have been allocated.

When a request is made, MySQL traverses the free list, splits a block, moves the remainder to used , and returns the requested portion.

Allocation Example

Initialize MEM_ROOT (e.g., min_malloc = 32, block_num = 4, block_size = 1000).

Request memory: if free list is empty, allocate four blocks of size 250 from the OS and build the free list.

Allocate 220 bytes: take the first free block, split it into a 220‑byte used block and a 30‑byte remainder, update left values, and move the used block to the used list.

Release Example

Traverse the used list to find the block to free (e.g., the 220‑byte block).

Merge its remaining space back into the corresponding free block, restoring the original 250‑byte block.

Insert the merged block into the free list.

Execution Process of the GROUP BY Query

Create a temporary table with columns viewed_user_age and count(*), using viewed_user_age as the primary key.

Scan the auxiliary index idx_user_viewed_user to obtain primary‑key id values.

For each id, fetch the full row from the clustered index, extract viewed_user_age, and update the temporary table: insert a new row if the age is not present, otherwise increment the count.

After scanning, sort the temporary table by viewed_user_age using the sort buffer and return the result set.

Optimization Plan

To avoid the temporary table and filesort, add a composite index covering the filtering and grouping columns:

ALTER TABLE `t_user_view`
ADD INDEX `idx_user_age_sex` (`user_id`, `viewed_user_age`, `viewed_user_sex`);

After adding the index, EXPLAIN shows that MySQL can satisfy the query directly from the index, eliminating the Using temporary and Using filesort steps.

Summary

The article dissected a GROUP BY query using EXPLAIN, explained how MySQL creates and uses memory and disk temporary tables, described the internal MEM_ROOT allocation mechanism, and presented an index‑based optimization that removes temporary tables and sorting.

Thought Question

Why does the composite index idx_user_age_sex prevent the creation of a temporary table and the use of the sort buffer? Explain based on index lookup principles.