Subject Re: [ib-support] Adding index does nothing to select disctinct
Author Helen Borrie
At 10:39 AM 13-09-02 +1000, you wrote:
>Svein Erling Tysvaer wrote:
> > Hi Mark!
> > Yes, most likely. At least I cannot see anything you write here that
> should
> > influence the speed very much. What is your WHERE clause like? If you add
>OK, what you're saying is that SELECT DISTINCT doesn't know how to take
>advantage of indexes. Maybe that can be an enhancement for version 2.0.

It's hard to see how it could, since SELECT DISTINCT has to get a distinct
occurrence of all the columns named in the output set. The logic of that
involves intermediate sets. Even in your case, where you have only one
column, it still has to walk the whole table to eliminate the duplicates,
because you have no limiting clause.

I avoid SELECT DISTINCT if speed matters, and structure the table and/or
the query so that I can use a WHERE clause. In your example, I'd certainly
have a JOB table where JOB_NUMBER is unique.

But if you can't do that, at least limit the rows by some means (e.g. date)
so that your query has a smaller set to walk.