sql - What's faster, SELECT DISTINCT or GROUP BY in MySQL?

ID : 10355

viewed : 33

Tags : mysqldatabasegroup-bydistinctmysql

Top 5 Answer for sql - What's faster, SELECT DISTINCT or GROUP BY in MySQL?

vote vote

91

They are essentially equivalent to each other (in fact this is how some databases implement DISTINCT under the hood).

If one of them is faster, it's going to be DISTINCT. This is because, although the two are the same, a query optimizer would have to catch the fact that your GROUP BY is not taking advantage of any group members, just their keys. DISTINCT makes this explicit, so you can get away with a slightly dumber optimizer.

When in doubt, test!

vote vote

88

If you have an index on profession, these two are synonyms.

If you don't, then use DISTINCT.

GROUP BY in MySQL sorts results. You can even do:

SELECT u.profession FROM users u GROUP BY u.profession DESC 

and get your professions sorted in DESC order.

DISTINCT creates a temporary table and uses it for storing duplicates. GROUP BY does the same, but sortes the distinct results afterwards.

So

SELECT DISTINCT u.profession FROM users u 

is faster, if you don't have an index on profession.

vote vote

78

All of the answers above are correct, for the case of DISTINCT on a single column vs GROUP BY on a single column. Every db engine has its own implementation and optimizations, and if you care about the very little difference (in most cases) then you have to test against specific server AND specific version! As implementations may change...

BUT, if you select more than one column in the query, then the DISTINCT is essentially different! Because in this case it will compare ALL columns of all rows, instead of just one column.

So if you have something like:

// This will NOT return unique by [id], but unique by (id,name) SELECT DISTINCT id, name FROM some_query_with_joins  // This will select unique by [id]. SELECT id, name FROM some_query_with_joins GROUP BY id 

It is a common mistake to think that DISTINCT keyword distinguishes rows by the first column you specified, but the DISTINCT is a general keyword in this manner.

So people you have to be careful not to take the answers above as correct for all cases... You might get confused and get the wrong results while all you wanted was to optimize!

vote vote

67

Go for the simplest and shortest if you can -- DISTINCT seems to be more what you are looking for only because it will give you EXACTLY the answer you need and only that!

vote vote

51

well distinct can be slower than group by on some occasions in postgres (dont know about other dbs).

tested example:

postgres=# select count(*) from (select distinct i from g) a;  count   10001 (1 row)  Time: 1563,109 ms  postgres=# select count(*) from (select i from g group by i) a;  count 10001 (1 row)  Time: 594,481 ms 

http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks_I

so be careful ... :)

Top 3 video Explaining sql - What's faster, SELECT DISTINCT or GROUP BY in MySQL?

Related QUESTION?