Re: Why DISTINCT is costly?

From: Noons <wizofoz2k_at_yahoo.com.au>
Date: Wed, 14 Sep 2005 23:52:04 +1000
Message-ID: <43282b05$0$12651$5a62ac22@per-qv1-newsreader-01.iinet.net.au>

Robert Klemme apparently said,on my timestamp of 14/09/2005 11:30 PM:

>
> Which one apart from the cost and the SORT GROUP BY vs. SORT UNIQUE. Did
> I overlook something?

IIRC, there was a significant difference in bytes processed? The text was a bit mangled due to wrapping, maybe I misread it.

>>But the best solution is always to avoid the DISTINCT.

>
>
> It's certainly good to have these rules of thumb. However me thinks it's
> even better to understand why these rules apply. That's why I ask.

Why they apply is simple: DISTINCT forces a unique sort, no matter what. You may be able to slightly speed the sort with indexes, but it ain't never gonna be as fast as joining to the necessary tables to avoid the duplicates in the first place.

If a join solves the problem one is trying to solve, then it is a preferable solution. There are a few examples around of this kind of design problem, I'll see if I can find some URLs for you.

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
wizofoz2k_at_yahoo.com.au.nospam

Received on Wed Sep 14 2005 - 08:52:04 CDT