Hi Michael!

>I have a table containing some 33.879.139 records.
>
>If I do a simple select like this:
>
>
>Select Dato from Transaktioner
>Where
> VareFrvStrNr='090179'
> AND Art=11
> And Transaktioner.Afdeling_ID<>'99999'
>
>it fecthes 11 records within 0.3 seconds.
>It will use the index on VareFrvStrNr according to the plan.
>PLAN (TRANSAKTIONER INDEX (TRANS_VAREFRVSTRNR))
>
>If I instead uses this SQL
>
>Select Min(Dato)As MinimuSTG from Transaktioner
>Where
> VareFrvStrNr='090179'
> AND Art=11
> And Transaktioner.Afdeling_ID<>'99999'
>
>It takes at least 3 seconds. Often more.
>Now the plan is like this:
>PLAN (TRANSAKTIONER ORDER TRANS_DATO INDEX (TRANS_VAREFRVSTRNR))
>
>The TRANS_DATO index has almost no duplicates.
>The TRANS_VAREFRVSTRNR can have some dublicates.
>
>Why is there such a big difference is the performance?

I don't know, though I guess 090179 isn't the VareFrvStrNr with the highest number of duplicates and since Firebird doesn't (yet) use histograms (I think), the optimizer doesn't know that using TRANS_DATO will slow down this query. If you used a common VareFrvStrNr (in this sense, NULL IS a value :o), then I guess the slow query might be quicker than the quick one to fetch all rows.

>can I do anything to optimize this?

Well, I would guess a variant of the old +0 trick would help:

Select Min(Dato+0) As MinimuSTG from Transaktioner Where ...

HTH,
Set