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
