Re: what is too many indexes

tam, > Some of our tables have 12, 14 , 16 or more indexes on them. I have always which is most likely too many. > being told and belived that too many indexes and indexes of the wrong kind > can slow queries down i.e. indexes can take up loads of disk space and can not necessarily slow down queries. it can do that if the optimizer chooses a not so good index for the query, but that is about it. the big thing about too many indices is that they take up space, (i am working with a client right now, and they have more than a few tables where the space taken up for the indices is more than the space taken up by the data!), and that they kill you on inserts, updates and deletes. every time you perform one of these operartions, it not only has to deal with the row that is being manipulated, but potentially *all* of the indices on the table. the real problem comes when after changing an index page, if that makes the tree imbalanced, then the engine must shuffle the index pages around to properly balance the tree. this takes processing power, and can turn a simple insert into a long affair. > become skewed etc .... Unfortunately, I cant find much info in the informix > manuals to support this in detail. Can anyone refer me to another manual or > source of information where I can find out more about indexes and > performance. the only thing i know of off hand is the tuning manual, but i'm only speaking from partial memory. it is scattered in bits and pieces throughout the manuals, and you will pick it up by reading enough about indices in various places throughout the manuals. when a table has that many indices on it, chances are good it is not a well designed table, conforming to at least 1st normal form, let alond 2nd and 3rd. the table most likely could be redesigned into 2 or more tables, usually giving better performance all the way around. note that this isn't always true though, as sometimes it is better to have denormalized tables for perfromance reasons. joins are more expensive operations, but if you are going after a much smaller subset of data, then they can also be much faster. it is a definate try something and test it out, try something else... ad nauseum. hope that helps. mickm -- ----------------------------------------------------------------------- This is a signature file. This is only a signature file. Had this been an actual piece of useful information, you would have been instructed on what to do with it. -----------------------------------------------------------------------