When to Use SELECT DISTINCT vs GROUP BY in MySQL: Features, Performance, and Examples

MySQL provides two primary ways to retrieve unique rows: SELECT DISTINCT for deduplication and GROUP BY for grouping with aggregation. Both clauses perform internal grouping, but they differ in purpose, supported features, and performance characteristics.

SELECT DISTINCT

Removes duplicate rows from the result set, returning only unique combinations of the selected columns. NULL values are treated as a single distinct value.

Syntax

SELECT DISTINCT column1, column2
FROM table_name;

Key points

Can deduplicate on one or multiple columns.

Simple syntax; no aggregation functions.

Efficient when only uniqueness is required, especially on tables without suitable indexes.

GROUP BY

Groups rows that share the same values in specified columns and typically combines them with aggregate functions such as COUNT, SUM, AVG. It also supports HAVING filters on grouped results.

Syntax

SELECT column1, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...;

Key points

Enables aggregation and summarization of each group.

More complex syntax due to optional WHERE and HAVING clauses.

Prior to MySQL 8.0, GROUP BY performed an implicit sort, adding a filesort operation and extra cost. MySQL 8.0 removed this implicit sorting, narrowing the performance gap with DISTINCT.

Example schema

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  city VARCHAR(255) NOT NULL
);
INSERT INTO customers VALUES
  (1, 'John Doe', 'New York'),
  (2, 'Jane Smith', 'London'),
  (3, 'Mike Brown', 'Paris'),
  (2, 'Jane Smith', 'London'); -- duplicate

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  product VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO orders VALUES
  (1, 1, 'Phone', 100.00),
  (2, 2, 'Laptop', 500.00),
  (3, 1, 'Tablet', 200.00),
  (4, 2, 'Watch', 150.00);

Usage examples

1. Retrieve unique cities

SELECT DISTINCT city FROM customers;

Result: New York, London, Paris.

2. Count orders per city

SELECT c.city, COUNT(*) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.city;

Result: London 2, New York 2.

Performance comparison

When an indexed column is used, SELECT DISTINCT and GROUP BY can share the same execution plan, e.g., EXPLAIN shows Using index for group‑by. Without indexes, DISTINCT may be faster on pre‑MySQL 8.0 because GROUP BY adds an implicit filesort. From MySQL 8.0 onward, the optimizer no longer forces this sort, making the two clauses similarly efficient on large, unindexed datasets.

Choosing the appropriate clause

Use SELECT DISTINCT when you only need to eliminate duplicate rows and return unique values.

Use GROUP BY when you need to aggregate data (e.g., COUNT, SUM, AVG) or apply HAVING filters on grouped results.

Conclusion

SELECT DISTINCT

is ideal for straightforward deduplication, while GROUP BY provides powerful grouping and aggregation capabilities. Since MySQL 8.0 the performance gap has narrowed, but semantic clarity and the need for aggregation should guide the choice between the two clauses.