Why MySQL Uses Temporary Tables for GROUP BY and How to Optimize It

When the MySQL query optimizer cannot find a suitable index to use, it must first read the required rows and then use a temporary table to complete the GROUP BY operation.

Example:

EXPLAIN SELECT max(gmt_create) FROM group_message WHERE group_id > 1 and group_id < 10 GROUP BY user_id \G
********** 1. row *********
id: 1
select_type: SIMPLE
table: group_message
type: range
possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 32
Extra: Using where; Using index; Using temporary; Using filesort

The execution plan shows that MySQL used an index to locate the rows, then created a temporary table and performed a filesort to obtain the GROUP BY result.

In the example, group_id is a range condition rather than a constant, and the GROUP BY column is user_id. Because the index order cannot assist the grouping, MySQL first scans the index range, stores the rows in a temporary table, and finally sorts and groups them.

To optimize this situation, ensure that sort_buffer_size is large enough for the sort operation, and try to avoid large result‑set GROUP BY queries. If the temporary table exceeds the configured size, it will be written to disk, causing a dramatic performance drop.