index question

Current: O/S: HP/UX IDS: 9.4FC3 New (soon): O/S RHEL 5 IDS 11.1FC3 (can't go any newer) We are trying to get some 'expert' answers on creation of indexes and how they are actually used in our queries. The main indexes were setup by the app developers (not us) and we have added a few to some key tables and wondering if we are using incorrect assumptions on usage. As a prime example, we have a table with 46 fields (let's call them 1-46 for illustration). It is one of our largest tables and the indexes are therefore also taking up quite a bit of space. The primary key is 1,2,3,4,5,6 (char(1),smallint,char(2),int,date,smallint) and there is a unique index for that constraint. 1 is one of 3 values, 2 is always the same value, 3 is 1 of about 12 values, 4 is the most unique, 5 is a date, 6 is the seq for that date. 1,2,3,4 is the primary data elements that link this table to most others (although there are no true foreign keys established) and are used in most queries. There are also several other multi-field indexes such as: 1,2,3,4,5,7; 1,2,3,4,8,9; , 1,2,3,4,5,6,20 There are also several single field indexes or multi-fields that don't include 1,2,3, or 4 such as: 19; 20; 32; 8,9; 5,7 Will indexes (1,2,3,4,5,6) & (5,7) be combined if a query was run using a where with 1,2,3,4,5,7 without needing the specific 1,2,3,4,5,7 index? Or for that matter, would they be combined if I just had the 1,2,3,4,5,6 PK index and a single field index on 7? Would the 1,2,3,4,5,6 index be used if only 5 & 6 are in the where? Is there a good resource that we could read to get a better understanding of 'best' practices' for index creation/usage? What we would like to do is have the PK index and then an single field index for all the other fields that we use in where clauses, but not sure if that will work for us or give us the best searching speed. TIA, Randy

OK, you are on Informix 11.10 so you are missing two key features that were introduced in 11.70 that would simplify your schema quite a bit. I will address that later, but for now here's the scoop for the version you are currently on: - Prior to 11.70 Informix will only use one index for each table in a query, so the idea of just having the primary key index and several singleton indexes will not work for you. - Prior to 11.10 Informix could only perform direct key lookups in an index on the leading columns in the key. However, the remaining key columns can be used as filters to further reduce the number of data rows that need to be examined. But you are running 11.10 which has index-self-join available. So, if you have an index on 1, 2, 3, 4, 5, 6, 7 then queries on 1, 2, 3, 4, 5, 7 will make equality lookups on 1, 2, 3, 4, 5 and then examine all of the selected keys for the search value for column 7. This is a bit better than having to retrieve all of the data rows that match 1, 2, 3, 4, 5 in order to filter for 7 however. If you upgrade to 11.70 Informix can use multiple indexes for each table in a join which will allow you to simplify your indexes a reduce the total storage. You could also take good advantage of the new index type known as a Forest of Trees Index which is very efficient for keys that begin with columns that have low selectivity like your primary key. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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. On Mon, Dec 27, 2010 at 9:40 AM, Kennedy, Randy <RKennedy@scottsdaleaz.gov>wrote: > Current: O/S: HP/UX IDS: 9.4FC3 > New (soon): O/S RHEL 5 IDS 11.1FC3 (can't go any newer) > > We are trying to get some 'expert' answers on creation of indexes and > how they are actually used in our queries. The main indexes were setup > by the app developers (not us) and we have added a few to some key > tables and wondering if we are using incorrect assumptions on usage. > > As a prime example, we have a table with 46 fields (let's call them 1-46 > for illustration). It is one of our largest tables and the indexes are > therefore also taking up quite a bit of space. > > The primary key is 1,2,3,4,5,6 > (char(1),smallint,char(2),int,date,smallint) and there is a unique index > for that constraint. > 1 is one of 3 values, 2 is always the same value, 3 is 1 of about 12 > values, 4 is the most unique, 5 is a date, 6 is the seq for that date. > > 1,2,3,4 is the primary data elements that link this table to most others > (although there are no true foreign keys established) and are used in > most queries. > There are also several other multi-field indexes such as: 1,2,3,4,5,7; > 1,2,3,4,8,9; , 1,2,3,4,5,6,20 > There are also several single field indexes or multi-fields that don't > include 1,2,3, or 4 such as: 19; 20; 32; 8,9; 5,7 > > Will indexes (1,2,3,4,5,6) & (5,7) be combined if a query was run using > a where with 1,2,3,4,5,7 without needing the specific 1,2,3,4,5,7 index? > Or for that matter, would they be combined if I just had the 1,2,3,4,5,6 > PK index and a single field index on 7? > > Would the 1,2,3,4,5,6 index be used if only 5 & 6 are in the where? > > Is there a good resource that we could read to get a better > understanding of 'best' practices' for index creation/usage? > > What we would like to do is have the PK index and then an single field > index for all the other fields that we use in where clauses, but not > sure if that will work for us or give us the best searching speed. > > TIA, > Randy > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5b15b764c2d04986612fd