Re: Why DISTINCT is costly?

From: Ed Prochak <ed.prochak_at_magicinterface.com>
Date: 12 Sep 2005 06:41:50 -0700
Message-ID: <1126532510.060522.144480@g47g2000cwa.googlegroups.com>

Robert Klemme wrote:
> sim wrote:
> > In most cases it's better to use a group by than distinct. That should
> > decrease the cost of your queries.
>
> Why? The task of identifying duplicates remains the same...
>
> robert

Right, it doesn't reduce the cost. I always suggest replacing

SELECT DISTINCT x ...

with
SELECT x,COUNT(*) ... GROUP BY x ;

especially for adhoc queries. Then at least you get back some information about the duplicates.

For production, you can either refine your query to get back unique information (preferred IMHO), or ignore the extras based on an informed choice.

Ed Received on Mon Sep 12 2005 - 08:41:50 CDT