Re: Deadly sins againts database performance/scalability

"Cris Carampa" <cris119_at_operamail.com> a écrit dans le message news: bq7aai$3pm$1_at_panco.nettuno.it...
> 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?
>
> --
> Cris Carampa (spamto:cris119_at_operamail.com)
>
> "Poveri fanatici comunisti, noglobal e affetti dalla sindrome
> anti-microsoft" (gli utenti Linux secondo un poster di ICOD)
>

Neither right nor wrong.
Those 2 queries are different in purpose.

select distinct should only be used when a natural key doesn't exist to get unique rows. This situation is *always* the consequence of a poor datamodel design.

I use personnaly select distinct when i want to test the cardinality of a column :
select count(*), count(distinct my col) from bla bla

HTH
Alkos. Received on Mon Dec 01 2003 - 03:07:30 CST