it's depend where you filter will be the most evident
ie : on VareFrvStrNr or on Dato ...

for exemple it their is zillions reccord with VareFrvStrNr = '090179'
then it's better to
use the index on Dato and read the data in the dato order still will see
one row with
VareFrvStrNr = '090179'

on the other way, if few rec with VareFrvStrNr = '090179', it's better
for the engine
to retrieve ALL the record with VareFrvStrNr = '090179' and look in it
the low dato ...

ok firebird as this great hability to use 2 index :) but not always so good
this what he try to do in your query

the firebird engine can not be all the time cleaver to know with
strategy to use !

so in this way you can specify the plan in you query :)

On 2/1/2012 2:49 PM, Michael Vilhelmsen wrote:
>
> Hi
>
> 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?
> can I do anything to optimize this?
>
> Michael
>
>


[Non-text portions of this message have been removed]