Re: Overhead on Keys

>From: uunet!gatech!ncar.UCAR.EDU!asuvax!anasaz!qip.naomi (Naomi Walker) >Message-Id: <9202172332.AA14289@qip> >Subject: Overhead on Keys >To: informix-list@rmy.emory.edu (Informix List) >Date: Mon, 17 Feb 92 16:32:27 MST >X-Informix-List-Id: <list.852> > >We have a table that has currently 20k rows (1700 bytes), and two keys. >Index one is on field one thru 4. Index two is on field 5. > >This table will very soon grow to at least 50k rows. I am considering >adding another key on field one and five, but am concerned about the >overhead of another key, since speed is a real factor in our application. > >Does anyone know how to compute the overhead for a key? Yes. Are you using Standard Engine or OnLine (or, perish the thought, Turbo)? Actually, there isn't all that much difference between the calculations, and the calculations are only approximate. Also, are you concerned by the disk space of the CPU time that is used in maintaining the extra key? There is documentation in the OnLine Programmers Guide, and in the new "Guide to SQL: Reference" book on the space used by an index. The calculations are quite complex, and not singularly accurate. I don't, yet, have a better formula, so maybe I shouldn't throw stones inside a glass house. If you have neither of these documents, I can send you a terse summary of these two calculation methods. Calculating the time overhead is near enough impossible. You try it and see whether the performance is satisfactory. Don't forget that the performance varies with key size and number of rows in the table and number of duplicate values in the index, and the order in which the keys are inserted, deleted, or updated (and on whether the last full moon fell on a Thursday, ... :-}) >We are running Informix 4.1, on a Pyramid MIS S Server, and primarily >using esql/c for all our code. The space used does not depend on whether you are using ESQL/C or I4GL or ISQL or ...; it only depends on the database engine. Yours sincerely, Jonathan Leffler (johnl@obelix.informix.com)