How MySQL Implements DISTINCT: Index Use, Temporary Tables, and Optimization
Distinct in MySQL works like GROUP BY but returns only one row per group; it can use index scans like GROUP BY, falls back to a temporary table when indexes aren't sufficient, and unlike GROUP BY it avoids sorting, making index optimization crucial for large result sets.
In MySQL, DISTINCT is essentially similar to GROUP BY, differing only in that after grouping it returns a single record per group.
Therefore, the implementation of DISTINCT is almost the same as GROUP BY; it can be performed via an index scan. When an index alone cannot satisfy the DISTINCT operation, MySQL resorts to a temporary table, but it does not perform a filesort on that temporary table.
Example:
EXPLAIN SELECT DISTINCT group_id FROM group_message\G
****** 1. row ******
id: 1
SELECT_type: SIMPLE
table: group_message
type: range
possible_keys: NULL
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 10
Extra: Using index for group-byThis query is executed entirely using an index, and its execution plan shows that the same method as GROUP BY is used.
Optimization of DISTINCT follows the same ideas as GROUP BY: make full use of indexes, and when indexes cannot be used, avoid applying DISTINCT on large result sets.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
Java High-Performance Architecture
Sharing Java development articles and resources, including SSM architecture and the Spring ecosystem (Spring Boot, Spring Cloud, MyBatis, Dubbo, Docker), Zookeeper, Redis, architecture design, microservices, message queues, Git, etc.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.