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]
