Subject Re: [firebird-support] How to index this table
Author Pierre Y.
Post date 2019-11-18T10:50:13Z
Hi Karol,

Thank you for your advices.

Adding "simple" indexes on "MAGASIN" and "CAISSE" makes query running more slowly : 49s with indexes vs 44s without

When the table is indexed on (ANNULE, TYPE_MOUVEMENT, DATEHEURE) they are not used at all.

Regards,

--
Pierre Yager


On Fri, Nov 15, 2019 at 7:26 PM Karol Bieniaszewski liviuslivius@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
 

Hi

 

Your problem is that you have (X or X) and (Y or Y)

Firebird cannot use composite index here.

Maybe it can somehow make something like (X1 and Y1) or (X1 and Y2) or … or (X1 and YN) or (X2 and Y1) or (X2 and Y2) …. (XN and YN)

But it will be never optimal.

 

Create separate indexes for magasin and caisse – then Firebird can use BITMAP OR + BITMAP AND

 

Another optimisation will be instead of multiple OR put all values into temporary table and then join with it.

Any other optimisations depend on value propagation in particular fields involved in the where clause.

 

Regards,

Karol Bieniaszewski