Firebird/InterBase does not use index to speed up DISTINCT.

It was announced (and implemented) as new feature in IB5 (5.0, 5.1, 5.5)
(this is from Release_Notes:
"The DISTINCT operator has been optimized to use an index where possible."),
but it was silently dropped from IB5.6 and higher
(_probably_ because the implementation was flawed and did not work
correctly for some types of queries).

As a workaround (as others mentioned) you can
- restrict number of visited rows in WHERE clause
(probably not suitable for you)
- use extra table to store distinct values for Job_Number

It is also possible to write SP to speed up this type of queries
(in fact, I am preparing several documents about optimization
for my site, one of them specifically about Select Distinct,
with SP examples; unfortunately, I do not have free time
to finish it right now, because my regular job has higher priority).

Btw. I am looking for a new job (approx. after New Year).
Any employers interested ?

Ivan
http://www.volny.cz/iprenosil/interbase

> We have tables of factory work pieces that grow by hundreds or records daily. I
> want to be able to offer the users a selection of all jobs on rthe table. I
> tried doing
> SELECT DISTINCT(JOB_NUMBER) FROM SAWMEMEBRS
> with out an index on SAWMEMBERS(JOB_NUMBER)
> It was taking 6 secs +- a couple of seconds.
>
> Then I did
> CREATE INDEX SM_JOBNUMBER ON SAWMEMBERS(JOB_NUMBER)
> and there was no apparent speed benefit. I even quiet the test program, got back
> in,listed the indexes, swa that it was there, and still there was no increase in
> speed. That's longer than we'd like to make the users wait.
>
> Am i missing something?