|Re: [firebird-support] optimizing query
|Svein Erling Tysvaer
>dlr.status has 5 possible values, dlr.mtype has 2 possible values.Sorry Edwin, I just noticed this. Indexing a field with only five possible
>and the query plan is now
>PLAN SORT (JOIN (DLR DLR INDEX (DLR_STATUS_IDX),MT MT INDEX (RDB$PRIMARY29),T
values is generally a bad idea, and it may slow down your query a bit
unless 'delivery success' is relatively rare (though the problems with such
an index is more visible in case of updates and deletes). I don't know the
treshold for when creating an index is useful (until Helen or Ann jumps in
(please do), let's guess it is around 15 or 20 different values if the
distribution is even), but with any such field with low selectivity I
always add the PK to the index. The reason being that updates/deletes may
quicker identify where the row is located within the index.
After deleting your DLR_STATUS_IDX index, I recommend you try to prepare
your query without the OR 2=0, just to see if the optimizer choose a
similar plan or not. If it does, then just add the OR 2=0 back.