DISTINCT vs GROUP BY in MySQL: Performance and Usage Guide

The article compares DISTINCT and GROUP BY in MySQL, concluding that when an index can be used both statements have the same efficiency, but without an index DISTINCT is faster because GROUP BY may perform an implicit sort (filesort) that slows execution.

DISTINCT usage

SELECT DISTINCT columns FROM table_name WHERE where_conditions;

Example:

mysql> select distinct age from student;
+------+
| age |
+------+
| 10  |
| 12  |
| 11  |
| NULL|
+------+
4 rows in set (0.01 sec)

Multi‑column distinct:

SELECT DISTINCT column1, column2 FROM table_name WHERE where_conditions;

mysql> select distinct sex,age from student;
+--------+------+
| sex    | age |
+--------+------+
| male   | 10 |
| female | 12 |
| male   | 11 |
| male   | NULL|
| female | 11 |
+--------+------+
5 rows in set (0.02 sec)

GROUP BY usage

SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;

Single‑column group by example:

mysql> select age from student group by age;
+------+
| age |
+------+
| 10 |
| 12 |
| 11 |
| NULL|
+------+
4 rows in set (0.02 sec)

Multi‑column group by example:

SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;

mysql> select sex,age from student group by sex,age;
+--------+------+
| sex    | age |
+--------+------+
| male   | 10 |
| female | 12 |
| male   | 11 |
| male   | NULL|
| female | 11 |
+--------+------+
5 rows in set (0.03 sec)

Group by can also be used with a single column while selecting other columns, e.g.:

mysql> select sex,age from student group by sex;
+--------+-----+
| sex    | age |
+--------+-----+
| male   | 10 |
| female | 12 |
+--------+-----+
2 rows in set (0.03 sec)

Underlying principle

In most cases DISTINCT can be regarded as a special form of GROUP BY ; both rely on grouping operations and can use index scans (range, index, or covering index). The EXPLAIN output for both shows "Using index for group‑by" when an index is applicable.

mysql> explain select int1_index from test_distinct_groupby group by int1_index;
... Using index for group‑by ...

mysql> explain select distinct int1_index from test_distinct_groupby;
... Using index for group‑by ...

Before MySQL 8.0, GROUP BY performed an implicit sort, which could cause a temporary table and a filesort, dramatically reducing performance for large result sets.

mysql> explain select int6_bigger_random from test_distinct_groupby GROUP BY int6_bigger_random;
... Using temporary; Using filesort ...

Since MySQL 8.0 the implicit sorting was removed, so when no index is usable the performance of DISTINCT and GROUP BY becomes almost identical.

Why prefer GROUP BY

Clearer semantics for grouping.

More flexible for complex processing (HAVING, aggregation functions, etc.).

DISTINCT applies to all selected columns, while GROUP BY lets you group by specific columns and compute aggregates on others.