IDS Forum

Re: index question

Posted By: Art Kagel
Date: Monday, 27 December 2010, at 10:34 a.m.

In Response To: index question (Kennedy, 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

Messages In This Thread

• index question
  Kennedy, Randy -- Monday, 27 December 2010, at 9:40 a.m.
  • Re: index question
    Art Kagel -- Monday, 27 December 2010, at 10:34 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.