Joined: 22 Apr 2006 Posts: 6258 Location: Mumbai, India
I'll start with,probably, what you want to listen - in a way, adding indexes to a table slows down insert and updates to that table, why? Because, each of these changes have to be applied not only to the actual data but the index pages also. And it will surely (should be acceptable, right?) eats up more disk space. If your database is small and your tables have few indexes on them, then adding a new index, which might not be required at all though, will probably not be noticed on modern fast machines. If your data is huge, very huge, your table is heavily indexed or you are runing on old machnines (actuators are not that good) then sometimes you have to make intelligent choices about which indexes are the most important to the performance of your program. The indexes for seldom-used queries sometimes have to be removed for the sake of more important indexes and the overall performance of updates to the table.
Because action queries on a table are affected by the number of indexes that table has, a table that is updated constantly by multiple users may not be able to have as many indexes as one that stays very static - so yes, as others have siad - yep, it depends.