Re: Creating indexes

> Date: 15 Mar 95 12:44 +0100 > From: Torben Skals Pedersen <tsp@dou.dk> > To: informix-list@rmy.emory.edu > Subject: Creating indexes > > Hello > > When you create an index consisting of i.e. (date,number), is the order > (date, number) or (number, date) of importance ? > > What if there are more elements in the index ? > > Do you have any rules of thumb ? > > ----------------------------------------------------------------------------- > Torben Skals Pedersen E-mail : tsp@dou.dk > Dept. of Clin. Chem., Odense University Hospital Phone : (+45) 65412834 > Sdr. Boulevard 29, DK-5000 Odense C, Denmark Telefax : (+45) 65411911 Rules of thumb: 1. If you expect to use some part(s) of a compound index without the other(s) in a WHERE clause, put the parts to be used alone at the front of the index. Informix can use leading (but not trailing) columns of a compound index as if they were a separate index. 2. For the reason stated above, do not have an index on (A,B) if you already have an index on (A,B,C). You will pay the cost to maintain the (A,B) index, but derive no additional benefit. 3. If you expect to use columns of a compound index in ORDER BY clauses, list the columns in the same order in both the index and the ORDER BY. 4. Never use your thumb as a rule: It is an inexact measure, and you might hit it with a hammer. Regards, Alan +---------------------------+-----------------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Martin Marietta, SLS | Voice: 303-977-9998 | | P.O. Box 179, M/S 3810 | Standard disclaimers apply. Cutesy ones, too. | | Denver, CO 80201-0179 USA | Your mileage may vary. Void where prohibited. | +---------------------------+-----------------------------------------------+