Path: utzoo!utgpu!watmath!clyde!att!osu-cis!tut.cis.ohio-state.edu!mailrus!ames!lll-lcc!pyramid!infmx!davek
From: davek@infmx.UUCP (David Kosenko)
Newsgroups: comp.databases
Subject: Re: need HELP with index problem
Keywords: index informix
Message-ID: <710@infmx.UUCP>
Date: 27 Dec 88 16:35:59 GMT
References: <8973@cit-vax.Caltech.Edu>
Organization: Informix Software Inc., Menlo Park, CA.
Lines: 41

In article <8973@cit-vax.Caltech.Edu>, 
	citron@cit-vax.Caltech.Edu (Mark C. Citron) writes:
> Hope someone can understand what is going wrong here. I have a table in
> a database that has three indices: 
> an index of unitnumber
> 
> there are about 50,000 rows in the table. In about 90% of the rows the
> unitnumber is 0. In time these will be changed to other numbers (the
> slots will be "filled"). 
 [ problem description deleted ]

	An index on a field that has 90% duplicate entries is only
useful when accessing (or updating) records where the value is other
than that field; the index can be used to get to the record quickly.
Thus, update where unitnumber = 1 (or any value other than 0) will
work quite nicely.  When the value is 0, however, the index becomes
not only useless, but detrimental; a sequential access would be better
in this case.  Unfortunately, the optimizer does not have the means to
detect this (at least not now). Using  the index to grab all those
records means a disk read (often) for each index entry, thus the poor
performance.  This is expected behavior.

	You may want to reconsider the index on unitnumber.  If most
of your accesses are where unitnumber = 0, the index is doing more
harm than good.  If, however, you tend to access where unitnumber != 0
most often, you probably want to keep it around, though at the cost
of the performance you are now seeing.

	Also, if you can provide any values for the other fields indexed
the queries may run faster (i.e., cause other indexes to be used when
unitnumber = 0 - if you can provide values matching the composite index,
it would be best since it is unique).

	Hope this helps.

Dave
-- 
Disclaimer:  The opinions expressed herein |"As we hang beneath the heavens
are by no means those of Informix Software | and we hover over hell
(though they make you wonder about the     | our hearts become the instruments
 strange people they hire).                | we learn to play so well"