Composite Indexing strategy

(Possible duplicate post, sorry if it is the case) Hi all New to the community. I've stumble to a situation where I see multiple composite indexes with same fields, but changing order of the fields. Do you have an idea on any situation that this could make sense , like for ex for old legacy Informix server versions ? This is the case: 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);Does it make any sense in any specific situation, like partitioning or whatever ?! My understanding is the same as here in slides 39-40 Any help in identifying some situation that falls out of this is much appreciated Thanks G. ------------------------------ Gilberto Pe-Curto ------------------------------ #Informix

Gilberto: 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. At Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Hi Gilberto - yes, this does make sense, but does not mean that it's optimal. With some exceptions, indexes in Informix require the leading columns in the index to be specified. So, if you have a composite index on (a, b, c) then if your query only specifies b & c, but NOT a value for a, then the index will (probably) not be used...or if it is, it won't be used efficiently. Also, if the query specifies a & c only, the index will be used, but it will result in more reads than an index on (a, c, b). What I suspect is the case here, is that the table can be queried by different criteria at different times - maybe sometimes by company and store, and other times by just store, and other times by start_date and company, etc. Additional columns may have been added to the index also to help with key-only scans (where all values needed by the query are already in the index and so there's no need to read data pages - these can be very fast). The other option is that somebody just added every possible combination of columns to a variety of indexes...just in case they might be used! You can review the reads against the indexes themselves to see if an index is used a lot to give you an idea if the index is used much If you are using Informix 14, then you can see the data that an index was last used with oncheck -pT. If the index hasn't been used for a long time, then there's a good chance that it can be dropped. ------------------------------ Mike Walker ------------------------------

Hi Mike and Art Thanks a lot to both. Rgds G ------------------------------ Gilberto Pe-Curto ------------------------------

Gilberto, Art and Mike explained very well your situation. This is total nonsense, unless the goal is to take more disk space for indexes and/or make the decision harder to take for the query optimizer for each query. Many times, having heavy/complex indexes cost more than having simple and efficient ones. The only mandatory rule here is the existence of primary key and foreign keys which are not negociable. For composite indexes, Mike's statement is the right one (most selective column as index header), but index self-join somewhat compensates the fact of having a badly selective first column. Once again, sqltrace will be the final judge and will provide with undisputable facts: set sqltrace on (low is enough), and look how many buffer pages, disk pages and index are read to return the actual number of rows. The general idea is to compare the number of read pages Vs the actual number of rows returned. If you have tons of pages read return just a few rows, your indexes are either missing or not efficient. The general idea is to avoid unnecessary reads, because unnecessary reads take more cpu time. A selective and efficient index will show few index pages read, a bad one will show many pages read. Then you'll have to play with those figures to understand what the engine does exactly and give hints to have better indexes. You can also look with onstat -g ppf or equivalent in sysmaster to check whether the incriminated indexes are ever used Best regards, Eric Eric Vercelletto Data Management Architect and Owner / Begooden IT Consulting Board of Directors, International Informix Users group IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020 Tel: +33(0) 298 51 3210 Mob : +33(0)626 52 50 68 skype: begooden-it Google Hangout: eric.vercelletto@begooden-it.com Email: eric.vercelletto@begooden-it.com www : http://www.vercelletto.com www https://kandooerp.org