Re: Deadly sins againts database performance/scalability

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 28 Nov 2003 10:41:30 -0800
Message-ID: <1070044919.760049@yasure>

Frank wrote:

> Cris Carampa wrote:
>

>> Alkos wrote:
>>
>>> Most of the time, people use SELECT DISTINCT to get unique rows because
>>> they don't want to "bother" with the datamodel. They are lazy or 
>>> hurried so
>>> they don't care about possible
>>> keys, about WHERE clauses selectivity and all that kind of things so 
>>> quick
>>> and dirty solution to be sure to get unique rows : SELECT DISTINCT which
>>> implies a sort therefore more activity to get the same result as they 
>>> may
>>> have gotten quicker with a little bit work on the WHERE clause.
>>
>>
>>
>> I do use SELECT DISTINCT when I have just to pick up distinct values 
>> from a table, and I don't need to count them or performing group 
>> functions amongs columns in the table. I assume the performance of 
>> this statement:
>>
>> SELECT DISTINCT FOOCOL FROM FOOTAB ;
>>
>> is better than the performance of this one:
>>
>> SELECT FOOCOL, COUNT(FOOCOL) FROM FOOTAB GROUP BY FOOCOL ;
>>
>> Or am I wrong?
>>

> Consider
>
> select distinct e.first_name
> from employees e, departments d;
>
> versus
>
> select e.first_name
> from employees e, departments d
> where e.department_id=d.department_id;
>
> Oversimplified, of course, but it's the idea.

I see no relationship between your two queries.

The first one is a Cartesian join returning distinct first names. The second is an inner join that returns all first names. They do completely different things.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Fri Nov 28 2003 - 12:41:30 CST