firebird-support - How to index this table

Subject	How to index this table
Author	Pierre Y.
Post date	2019-11-15T16:54:45Z

Hi,

I'm on Windows 7, running Firebird 3.0.4 :

I have this table hosting 1475813 records :

CREATE TABLE JOURNAL_CAISSE (
UID UNIQUEID NOT NULL /* UNIQUEID = CHAR(16) */,
NUMERO INTEGER NOT NULL,
TYPE_MOUVEMENT INTEGER NOT NULL,
DATEHEURE TIMESTAMP DEFAULT 'NOW' NOT NULL,
MAGASIN UNIQUEID NOT NULL /* UNIQUEID = CHAR(16) */,
CAISSE UNIQUEID NOT NULL /* UNIQUEID = CHAR(16) */,

PERIODE INTEGER NOT NULL,

ANNULE BOOLEAN DEFAULT false NOT NULL,
QUANTITE INTEGER DEFAULT 1 NOT NULL,
TYPE_REGLEMENT VARCHAR(30),
DEVISE CHAR(12),
VALEUR DECIMAL(10,4) DEFAULT 0.00 NOT NULL
);

With these constraints/indices :

ALTER TABLE JOURNAL_CAISSE

ADD CONSTRAINT JOURNAL_CAISSE_PK

PRIMARY KEY (UID);

ALTER TABLE JOURNAL_CAISSE

ADD CONSTRAINT JOURNAL_CAISSE_PERIODE_FK

FOREIGN KEY (PERIODE)

REFERENCES PERIODES (ID)

ON UPDATE CASCADE;

ALTER TABLE JOURNAL_CAISSE

ADD CONSTRAINT JOURNAL_CAISSE_TYPES_FK

FOREIGN KEY (TYPE_MOUVEMENT)

REFERENCES TYPES_MVT_JOURNAL (TYPE_ID)

ON UPDATE CASCADE;

CREATE INDEX JOURNAL_CAISSE_CAISSE ON JOURNAL_CAISSE (

TYPE_MOUVEMENT, CAISSE, DATEHEURE

);

CREATE INDEX JOURNAL_CAISSE_IDX ON JOURNAL_CAISSE (

PERIODE, MAGASIN, ZONE, CAISSE, NUMERO

);

I can't find a way to index this table properly so that this query can run quickly (for now it takes around 45s :

select

cast(2 as Integer) as TYPE_MVT,
TYPE_REGLEMENT, DEVISE, null as BANQUE,
sum(QUANTITE) as NOMBRE,
sum(VALEUR) as MONTANT
from JOURNAL_CAISSE
where (TYPE_MOUVEMENT in (2, 3))
and (ANNULE is false)

and (

(magasin=x'825469DD62BB4583B9F75C93E85FD4BF')

or (magasin=x'27917E1E9E99469ABB3B6B82075AD52A')

or (magasin=x'4AFB0F29F91644E8AC15B980FC4A17CF')

or (magasin=x'872CA352AD09459694F88FE51324CB35')

or (magasin=x'A2C3F9F5D1B1404293589068A8297EC0')

or (magasin=x'3050631347F04776BA6C08E10F164D76')

or (magasin=x'D277CB68C3154EACBC97B4BF95CC5B8F')

or (magasin=x'CA20DC918C9145FDA797043DCF477EA5')

or (magasin=x'79E0F6FE896A41F99D44D219A63CB511')

or (magasin=x'82717E0DA00D4DE2B82415A03F72846F')

or (magasin=x'189886B18FCD4998BEC33297ABF79E47')

or (magasin=x'376C73922868406CBD39540D47C7C1A7')

or (magasin=x'54C4FC9317724996989DC1C2BCA800CC')

or (magasin=x'C92CE52C9FFE48EA8B510888A0BD419C')

or (magasin=x'4B70E2D2232D47A6A74CE5C20ECDC941')

or (magasin=x'C893800B9A2E4E42BC34C47DEFF7135A')

or (magasin=x'87DA11B6C9724B47808B89E7338E9DFE')

)

and (

(caisse=x'0C949D9560234EE7B4824ABB93DA2386')

or (caisse=x'0F84C9F9A1E54C9C83AA1CEC3D337E9A')

or (caisse=x'19CFC91E81444B538640B4ED208D6AAC')

or (caisse=x'1B80E288A79C4569BEFE46D1FBD116B4')

or (caisse=x'22B06AC867AA4C87B813C78992105569')

or (caisse=x'29261F9D1B784723B7B474F056CE140C')

or (caisse=x'2AD2EEB3020641B28F53F03216169E36')

or (caisse=x'2BEB18A58A6F4EE19DA256B7E5A2C35E')

or (caisse=x'2D19A90D25AC4019B708D7A133A0FBE5')

or (caisse=x'2D7735DB379948A6A023C343A04E049C')

or (caisse=x'34402C4342474DBB8CB0446A09FEA15A')

or (caisse=x'3825FBA5A4B348EE9F604B2A2C994061')

or (caisse=x'396CF8284423444BA2509393BEDFD3B4')

or (caisse=x'3A3B742C2D394F589AA5EABAD0733B84')

or (caisse=x'3D53981F65E94100AE3779017C23F8A2')

or (caisse=x'448C6D67543A45859D7BAD5778D30DD9')

or (caisse=x'5996EAA0B442440C9C563BC4399F317C')

or (caisse=x'5A98039B179643238250E1BEB73F8FD6')

or (caisse=x'5F1357BFE2E64D309FC6889C07348A8A')

or (caisse=x'6D33F33594864CF2B32487797AC5D8A8')

or (caisse=x'6FF591B1D10C4A4C8C0904B629C42731')

or (caisse=x'70ABF3DAC8774484A172CBC8B65A98B4')

or (caisse=x'796C49B2D52B465A95D5B5D04EB43CEC')

or (caisse=x'7B729EC0919643DF8197407535B27DAF')

or (caisse=x'7F00F7E2FCD64946BF6C3C1DAE1D5716')

or (caisse=x'8579CAD9D7DC4C61B172F10D4E748891')

or (caisse=x'86F8AE15F55D40AF9E9C2D74B73C58FB')

or (caisse=x'8A63C3B11F774DC3AD599DE880FE5770')

or (caisse=x'8CFA2F21D96D49E3857E302542535C7A')

or (caisse=x'90495B8778D6482FA11D4BCA97629FDD')

or (caisse=x'909D44F58DF24077B51E3BB493E50C42')

or (caisse=x'9166B3D7319741669AB7DD434645426D')

or (caisse=x'936537DC96694885A6F7E661D3490C67')

or (caisse=x'9374E2AF14D749A2A2DAE63918A51D05')

or (caisse=x'985690F55F4244DAAED2063DB9A9EF08')

or (caisse=x'9AD383E3E6E842668481AFB3069C3812')

or (caisse=x'9CAA834580EA4B209DE4FBEC223AFE05')

or (caisse=x'A1CB7A2BB3B6465DB167216298EB45FD')

or (caisse=x'A42802A9027F4D8ABFDD4EBAEDC84A16')

or (caisse=x'ABE44B53C5454F12AE94925DEBB084F5')

or (caisse=x'AD068CF02DE54701AE2D572821B4F003')

or (caisse=x'B1B535D246554BA28EAC2F0BA6C50A6A')

or (caisse=x'B457957BFC55458C99B65C0471DC3CE6')

or (caisse=x'BBC68C6092924E7FB107DAA026B96BE6')

or (caisse=x'C29FE4826F014120BD451692052AA3ED')

or (caisse=x'C548320C0F094E8DBD0A0BE81D8754F3')

or (caisse=x'C5972AD1B56943C3A9612AC296167888')

or (caisse=x'D0454F093920400598DB91584A520CE1')

or (caisse=x'D33896B26CA64CCC8B0FDB6EEDE8005A')

or (caisse=x'D587BF15FA614573A7587D019C793B96')

or (caisse=x'D6F1C25294B042D7BF8BEA18AEF2EAC2')

or (caisse=x'DBE9AE3227874A69A49604BF34260169')

or (caisse=x'DE98446FCCE641278452009EAB8524F0')

or (caisse=x'E0858C2BF0D3442DA4DFFC461F32F804')

or (caisse=x'E147E1980D1343EA8DC46DD16B69ED1A')

or (caisse=x'E2CE21362B3D4038B2156BFA09A1BE34')

or (caisse=x'E4CFC80D32EE40578C40ACAC38056E50')

or (caisse=x'E9EC8A8305604952A1B21C9E01E25168')

or (caisse=x'EA5C17489E6C4461A5A4341960BB96C4')

or (caisse=x'EE5DA9CE231B4857B8DA603E01CB7AC2')

or (caisse=x'F4535DE4AA564132A9DE7CBC810940EB')

or (caisse=x'F45F75F18C14422081A0214E85A5D119')

or (caisse=x'F56F72D0221A435BB961A6F00926A5CB')

or (caisse=x'F59EFA9D34144818B1982213B3A767FD')

or (caisse=x'F5E09A25B6EA4EBD9DE291908E604655')

)

and (DATEHEURE between '2019-10-01' and '2019-11-01')

group by 1,2,3,4

Plan is : PLAN SORT (JOURNAL_CAISSE INDEX (JOURNAL_CAISSE_TYPES_FK, JOURNAL_CAISSE_TYPES_FK))

While this query returns in 313ms :

select
cast(2 as Integer) as TYPE_MVT,
TYPE_REGLEMENT,
DEVISE, null as BANQUE,
sum(QUANTITE) as NOMBRE,
sum(VALEUR) as MONTANT
from JOURNAL_CAISSE
where (TYPE_MOUVEMENT in (2, 3))
and (ANNULE is false)
and (PERIODE in (8851,8850,8849,8847,8846,8845,8844,8843,8842,8840))
group by 1,2,3,4

Plan is : PLAN SORT (JOURNAL_CAISSE INDEX (JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_TYPES_FK, JOURNAL_CAISSE_TYPES_FK))

First query runs in 15s instead of 45s (yes it is three times faster...) if I add this index :

CREATE INDEX JOURNAL_CAISSE_TYPE_DATEHEURE

ON JOURNAL_CAISSE (TYPE_MOUVEMENT, ANNULE, DATEHEURE);

Plan is : PLAN SORT (JOURNAL_CAISSE INDEX (JOURNAL_CAISSE_TYPE_DATEHEURE, JOURNAL_CAISSE_TYPE_DATEHEURE))

But I would like to know if you can help me to optmize filtering on CAISSE and MAGASIN columns.

Many thanks for your help, regards,

--
Pierre Yager