Menu
According to tips from MySQL performance wiki:
Don't use DISTINCT when you have or could use GROUP BY.
Can somebody post example of queries where GROUP BY can be used instead of DISTINCT?
791
Well, GROUP BY and DISTINCT have their own use. GROUP BY cannot replace DISTINCT in some situations and DISTINCT cannot take place of GROUP BY. It is as per your choice and situation how you are optimizing both of them and choosing where to use GROUP BY and DISTINCT.
DISTINCT is used to filter unique records out of all records in the table. It removes the duplicate rows. SELECT DISTINCT will always be the same, or faster than a GROUP BY.
As a general rule, SELECT DISTINCT incurs a fair amount of overhead for the query. Hence, you should avoid it or use it sparingly. The idea of generating duplicate rows using JOIN just to remove them with SELECT DISTINCT is rather reminiscent of Sisyphus pushing a rock up a hill, only to have it roll back down again.
GROUP BY lets you use aggregate functions, like AVG , MAX , MIN , SUM , and COUNT . On the other hand DISTINCT just removes duplicates. This will give you one row per department, containing the department name and the sum of all of the amount values in all rows for that department.
If you know that two columns from your result are always directly related then it's slower to do this:
SELECT DISTINCT CustomerId, CustomerName FROM (...)
than this:
SELECT CustomerId, CustomerName FROM (...) GROUP BY CustomerId
because in the second case it only has to compare the id, but in the first case it has to compare both fields. This is a MySQL specific trick. It won't work with other databases.
129
SELECT Code
FROM YourTable
GROUP BY Code
vs
SELECT DISTINCT Code
FROM YourTable
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
