CODEWITHSUNDEEP
sqlsql-server
wcm
wcm

Reputation: 9281

Which is better: Distinct or Group By

Which is more efficient?

SELECT  theField
FROM    theTable
GROUP BY theField

or

SELECT  DISTINCT theField
FROM    theTable

Upvotes: 16

Views: 15051

Answers (6)

auntyellow
auntyellow

Reputation: 2573

In MySQL, DISTINCT seems a bit faster than GROUP BY if theField is not indexed. DISTINCT only eliminate duplicate rows but GROUP BY seems to sort them in addition.

Upvotes: 0

dub
dub

Reputation: 1396

In your example, both queries will generate the same execution plan so their performance will be the same.

However, they both have their own purpose. To make your code easier to understand, you should use distinct to eliminate duplicate rows and group by to apply aggregate operators (sum, count, max, ...).

Upvotes: 36

Dana
Dana

Reputation: 32957

Hmmm...so far as I can see in the Execution Plan for running similar queries, they are identical.

Upvotes: 2

Frans Bouma
Frans Bouma

Reputation: 8357

Doesn't matter, it results in the same execution plan. (at least for these queries). These kind of questions are easy to solve, by enabling query analyzer or SSMS to show the execution plan and perhaps the server trace statistics after running the query.

Upvotes: 11

Greg Dean
Greg Dean

Reputation: 30057

In most cases, DISTINCT and GROUP BY generate the same plans, and their performance is usually identical

Upvotes: 7

Ken Yao
Ken Yao

Reputation: 1516

You can check the Execution Plan to look for the total cost of this statements. The answer may vary in different scenarios.

Upvotes: 5

Related Questions