Composite Indexing strategy

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. G. ------------------------------ Gilberto Pe-Curto ------------------------------ #Informix

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 spickett@us.ibm.com 617-899-7549 33 Years Informix User The current Informix Roadshow presentations are here: https://community.ibm.com/community/user/hybriddatamanagement/viewdocument/informix-1410xc2-features?CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=librarydocuments All presentations and the agenda used by the Roadshow can be found there. The current internal ZACS Informix Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en#!/wiki/Wf58c4c538dbf_45b4_b7a7_5003d0ceb79b/page/Informix The older Internal IMAZ CTP Informix Main Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en-us#!/wiki/Info%20Mgmt%20Client%20Technical%20Professional%20Resources%20Wiki/page/Informix Website for Internet Of Things https://www.ibm.com/internet-of-things/ Website for Informix https://www.ibm.com/analytics/us/en/technology/informix/

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 ------------------------------

Gilberto: 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 ------------------------------ Art Kagel ------------------------------

Hi Art I got it at the time thanks a lot Here it is the link