By vava


2009-02-24 12:04:19 8 Comments

If I have a table

CREATE TABLE users (
  id int(10) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  profession varchar(255) NOT NULL,
  employer varchar(255) NOT NULL,
  PRIMARY KEY  (id)
)

and I want to get all unique values of profession field, what would be faster (or recommended):

SELECT DISTINCT u.profession FROM users u

or

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

?

15 comments

@kolunar 2016-06-03 09:56:42

Here is a simple approach which will print the 2 different elapsed time for each query.

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();
SELECT DISTINCT u.profession FROM users u; --Query with DISTINCT
SET @t2 = GETDATE();
PRINT 'Elapsed time (ms): ' + CAST(DATEDIFF(millisecond, @t1, @t2) AS varchar);

SET @t1 = GETDATE();
SELECT u.profession FROM users u GROUP BY u.profession; --Query with GROUP BY
SET @t2 = GETDATE();
PRINT 'Elapsed time (ms): ' + CAST(DATEDIFF(millisecond, @t1, @t2) AS varchar);

OR try SET STATISTICS TIME (Transact-SQL)

SET STATISTICS TIME ON;
SELECT DISTINCT u.profession FROM users u; --Query with DISTINCT
SELECT u.profession FROM users u GROUP BY u.profession; --Query with GROUP BY
SET STATISTICS TIME OFF;

It simply displays the number of milliseconds required to parse, compile, and execute each statement as below:

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

@user2832991 2015-07-16 23:24:28

This is not a rule

For each query .... try separately distinct and then group by ... compare the time to complete each query and use the faster ....

In my project sometime I use group by and others distinct

@Grumpy 2015-06-09 09:12:37

After heavy testing we came to the conclusion that GROUP BY is faster

SELECT sql_no_cache opnamegroep_intern FROM telwerken WHERE opnemergroep IN (7,8,9,10,11,12,13) group by opnamegroep_intern

635 totaal 0.0944 seconds Weergave van records 0 - 29 ( 635 totaal, query duurde 0.0484 sec)

SELECT sql_no_cache distinct (opnamegroep_intern) FROM telwerken WHERE opnemergroep IN (7,8,9,10,11,12,13)

635 totaal 0.2117 seconds ( almost 100% slower ) Weergave van records 0 - 29 ( 635 totaal, query duurde 0.3468 sec)

@SquareCog 2009-02-24 12:09:22

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!

@Quassnoi 2009-02-27 15:11:17

DISTINCT will be faster only if you DON'T have an index (as it doesn't sort). When you do have an index and it's used, they're synonyms.

@rustyx 2015-01-25 15:03:34

The definition of DISTINCT and GROUP BY differ in that DISTINCT doesn't have to sort the output, and GROUP BY by default does. However, in MySQL even a DISTINCT+ORDER BY might still be faster than a GROUP BY due to the extra hints for the optimizer as explained by SquareCog.

@Pankaj Wanjari 2015-12-28 13:19:00

DISTINCT is much faster with large amount data.

@Lizardx 2016-02-21 22:32:23

I tested this, and found that on an indexed column, mysql, group by was about 6x slower than distinct with a fairly complicated query. Just adding this as a datapoint. About 100k rows. So test it and see for yourselves.

@kolunar 2016-06-03 09:25:31

see MySql - Distinct vs Group By <<< it says GROUP BY is better

@Daniel R 2014-06-09 19:37:04

If the problem allows it, try with EXISTS, since it's optimized to end as soon as a result is found (And don't buffer any response), so, if you are just trying to normalize data for a WHERE clause like this

SELECT FROM SOMETHING S WHERE S.ID IN ( SELECT DISTINCT DCR.SOMETHING_ID FROM DIFF_CARDINALITY_RELATIONSHIP DCR ) -- to keep same cardinality

A faster response would be:

SELECT FROM SOMETHING S WHERE EXISTS ( SELECT 1 FROM DIFF_CARDINALITY_RELATIONSHIP DCR WHERE DCR.SOMETHING_ID = S.ID )

This isn't always possible but when available you will see a faster response.

@Carlos 2014-02-11 18:27:20

In MySQL, "Group By" uses an extra step: filesort. I realize DISTINCT is faster than GROUP BY, and that was a surprise.

@OptilabWorker 2011-11-18 13:56:26

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 ... :)

@Ranjith 2013-06-21 01:09:18

Group by is expensive than Distinct since Group by does a sort on the result while distinct avoids it. But if you want to make group by yield the same result as distinct give order by null ..

SELECT DISTINCT u.profession FROM users u

is equal to

SELECT u.profession FROM users u GROUP BY u.profession order by null

@daniel.gindi 2013-05-16 10:49:53

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!

@a_horse_with_no_name 2013-09-15 10:44:08

Although this question is about MySQL it should be noted that the second query will work only in MySQL. Nearly every other DBMS will reject the second statement because it's an invalid use of the GROUP BY operator.

@daniel.gindi 2013-09-15 11:53:43

Well, "nearly" is a problematic definition :-) It would be much more helpful if you state a specific DBMS that you have tested to see that it generates an error for this statement.

@a_horse_with_no_name 2013-09-15 13:09:15

Postgres, Oracle, Firebird, DB2, SQL Server for starters. MySQL :sqlfiddle.com/#!2/6897c/1 Postgres: sqlfiddle.com/#!12/6897c/1 Oracle: sqlfiddle.com/#!12/6897c/1 SQL Server: sqlfiddle.com/#!6/6897c/1

@Fake Name 2014-07-24 11:30:26

FWIW, the second query works just fine in SQLite.

@Tim 2009-02-24 13:37:28

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!

@Ivan Dossev 2012-05-21 17:15:41

(more of a functional note)

There are cases when you have to use GROUP BY, for example if you wanted to get the number of employees per employer:

SELECT u.employer, COUNT(u.id) AS "total employees" FROM users u GROUP BY u.employer

In such a scenario DISTINCT u.employer doesn't work right. Perhaps there is a way, but I just do not know it. (If someone knows how to make such a query with DISTINCT please add a note!)

@Beep beep 2009-02-27 14:55:01

SELECT DISTINCT will always be the same, or faster, than a GROUP BY. On some systems (i.e. Oracle), it might be optimized to be the same as DISTINCT for most queries. On others (such as SQL Server), it can be considerably faster.

@Quassnoi 2009-02-27 14:50:08

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.

@Ariel 2014-08-20 03:21:18

You can add ORDER BY NULL to the GROUP BY to avoid the sort.

@amartynov 2009-02-24 12:20:18

It seems that the queries are not exactly the same. At least for MySQL.

Compare:

  1. describe select distinct productname from northwind.products
  2. describe select productname from northwind.products group by productname

The second query gives additionally "Using filesort" in Extra.

@SquareCog 2009-02-24 15:07:28

They are the same in terms of what they get, not in terms of how they get it. An ideal optimizer would execute them the same way, but MySQL optimizer is not ideal. Based on your evidence, it would seem that DISTINCT would go faster -- O(n) vs O(n*log n).

@vava 2009-02-25 00:17:13

So, "using filesort" is essentially bad thing?

@SquareCog 2009-02-25 15:36:41

In this case it is, because you don't need to sort (you would if you needed the groups). MySQL sorts in order to place the same entries together, and then get groups by scanning the sorted file. You just need distincts, so you just have to hash your keys while doing a single table scan.

@Ariel 2014-08-20 03:22:35

Add ORDER BY NULL to the GROUP BY version and they will be the same.

@tehvan 2009-02-24 12:09:28

If you don't have to do any group functions (sum, average etc in case you want to add numeric data to the table), use SELECT DISTINCT. I suspect it's faster, but i have nothing to show for it.

In any case, if you're worried about speed, create an index on the column.

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

36 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

25 Answered Questions

[SOLVED] Retrieving the last record in each group - MySQL

14 Answered Questions

[SOLVED] Select first row in each GROUP BY group?

23 Answered Questions

[SOLVED] Insert into ... values ( SELECT ... FROM ... )

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

20 Answered Questions

[SOLVED] LINQ's Distinct() on a particular property

2 Answered Questions

[SOLVED] Using group by on multiple columns

  • 2010-03-10 23:11:23
  • l--''''''---------''''''''''''
  • 851180 View
  • 883 Score
  • 2 Answer
  • Tags:   sql group-by

21 Answered Questions

[SOLVED] What's the best practice for primary keys in tables?

17 Answered Questions

Sponsored Content