Expand all | Collapse all

Composite Indexing strategy

  • 1.  Composite Indexing strategy

    Posted Wed November 04, 2020 07:49 AM
    Hi all,
    New to the group.

    Does anyone knows any reason for redundante composite indexes (multiple-columns) by just changing the field's order ?
    Like partitioning and whatever or legacy reasons on older versions of informix ?

    The reason is I came across this situation on a frequently accessed table we have in ERP database:
    CREATE INDEX i_2 ON table (company, store, item, start_date);
    CREATE INDEX i_3 ON table (item, store, company, start_date);
    CREATE INDEX i_4 ON table (start_date, company, store,item);
    CREATE INDEX i_5 ON table (item, start_date, store, company);
    CREATE INDEX i_6 ON table (store, company, start_date, item);

    And according to Lester Knutsen here on pages 39-40, he does not mention any reason that will justify that.


    I suggested removing redundancy, but someone raised this was justified.
    Thanks in advance
    Really wanted to optimize this indexing because the table has a lot of updates and the I/O propagating to all indexes is a huge overhead.


    Gilberto Pe-Curto

  • 2.  RE: Composite Indexing strategy

    Posted Wed November 04, 2020 09:05 AM
    Is this Baan or some 3rd party ERP package?
    Scott Pickett
    IBM Informix WW Technical Sales
    IBM Informix WW Cloud Technical Sales
    IBM Informix WW Cloud Technical Sales ICIAE
    IBM Informix WW Informix Warehouse Accelerator Sales
    Boston, Massachusetts USA
    33 Years Informix User
    The current Informix Roadshow presentations are here:
    The current internal ZACS Informix Page can be found here:

  • 3.  RE: Composite Indexing strategy

    Posted Fri November 20, 2020 11:13 AM

    Previously Informix would use index columns in left to right order so the first column in the index had to be part of the query filters.

    Evening now if querying by just the first column and wanting to select all the columns (covering index) then it would make sense to have multiple indexes.

    David Williams

  • 4.  RE: Composite Indexing strategy

    Posted Fri November 20, 2020 12:49 PM

    I sent a reply to this thread on Nov 3, but I can't find it here online, so here's what I said:

    Prior to the advent of multi-index scans and index-self-joins in v11.50 if you sometimes searched on only one column of an index key and it wasn't the first column, then the engine could not use that index for that filter. So it was common to have multiple indexes with the same set of columns but with the columns in different orders so that searches on any part of the search key could use one of those indexes. SO, yes, these are legacy style indexes needed to satisfy various searches efficiently in older releases of Informix.
    With the addition of index-self-join code Informix can use the second column in an index key for searching and filtering, so the pressure to have multiple indexes lessened. SO the i2, i_3 & i_4 indexes would suffice for all searches, though some searches would be a bit less efficient without the i_5 & i_6 indexes.
    The addition of multi-index scan code means that you can have singleton indexes on each column, such as:
    create index c_1 on table( company );
    create index c_2 on table( store );
    create index c_3 on table( item );
    create index c_4 on table( start_date );
    The engine could then use any one, two, three, or all four indexes to perform filtering and joining. This scheme would use about 1/6th the storage of the older indexes you show and execute most searches faster to boot.

    I would just add, that the best thing to do here is to make a current copy of the table and any other tables that are normally queries with it on a test server and run some typical queries both with the original indexes and with a smaller set of compound indexes and again with the singleton indexes and go with what works best!


    Art Kagel

  • 5.  RE: Composite Indexing strategy

    Posted Fri November 20, 2020 01:03 PM
    Hi Art

    I got it at the time
    thanks a lot

    Here it is the link