Subject RE: [firebird-support] Optimizing this select Query
Author Svein Erling Tysvær
>> Hm, this is just getting rid of using the index on sku - depending on
>> it's selectivity, this might be a way, but than, the index might be of
>> no use...
>> You might check combined indexes...
>It's avoiding to use the index in this particular query, it does not mean it has no use... In a diferent query it could be a good index.
>And I think this index is generated by a FK constraint, and could not be removed.
>How he can combine the index of two distinct tables ?

I don't think that's possible (I don't think you can reference other tables when using COMPUTED BY, though I've never tried), but in theory he could combine the indexes of SALEDATE and SALETYPE (which can be good for getting the last few percents of performance in critical areas, but at the cost of making it more difficult to understand and optimize the plans using this index (so I rarely use combined indexes myself)).

>To the OP:
>If you change the query to use fixed values instead of parameters does it change anything ? I cant remember if the FB optimizer take in count
>the range of the between, I don't think so, but you could give it a try.

It would have mattered if he had been using LIKE, using BETWEEN it shouldn't matter for Firebird versions <= 2.5 (It will probably matter for Firebird 3 since I think that version will include histograms).