Re: Having too many indexes on a table

Sukumar, Howdy. Additional indexing will slow inserts and deletes as the engine has to = create not just the data row, but and index entry for each index as well. = How much it slows down depends. Personally I have never had to remove = indexes because they were slowing down OLTP performance too much. Selects are generally improved by additional indexing, as long as the = indexes are in the right place ;-). If you want to learn more, then I would recommend as a first place to = start the excellent article written by Gavin Nour which is available at=20 http:/www.iiug.org/techinfo/articles/art_optimizer.html (I think that this is the right address, if this doesn't work, go to = www.iiug.org and follow your nose from there). After that I would recommend that you read chapter 4 of the Informix = Online Dynamic Server Performance guide several times over. It will tell = you just about everything that you want to know. (Get all your developers = to read both these sources as well, it will save you grief) A prime culprit in your situation is 'update statistics', but the detail = is all in the two places quoted above so I won't repeat it here. Good luck rgds Peter Tashkoff <tashkop@iname.com> Zespri International Limited Std Disclaimers Apply All rights reserved. No party may use this document to vilify another. Zespri New Zealand Kiwifruit, The World's Finest >>> Sukumar Konduru <sukumar@gte.net> 3/05/98 02:19:21 >>> Hi I have a table with 10 columns, and row size is 261 and index size is 117. I need indexes on these columns. 1. userid, startdate 2. userid, wcc 3. wcc, startdate 4. sesssionid ( I do not do query on this, But it is primary key for making row unique) I need to know about these problems 1. Is it slows down to have too many index. If it slows down for insertion and delete it is OK. But is it slows down "select " also? 2. If I decrease row size, i.e dropping some of coulmns, does it improve "select" performance. 3. Is there any order for creating indexes. 4. How to do update statistics for this combination Right now I do have indexes on 2,3,4(the listed one). I have data about 400,000 rows. It is slow many times. Could you please mail me sukumar@gte.net=20 Thank you Sukumar Konduru