Is there a downside to adding numerous indexes to tables?

Question

I am creating a new DB and was wondering if there was any downside to adding numerous indexes to tables that I think may require one.

If I create an index but end up not utilizing it will it cause any issues?

Answer 1

Indexes make it faster to search tables, but longer to write to. Having unused indexes will end come causing some unnecessary slow down.

Answer 2

Each Index :

• Takes some place, on disk, and in RAM
• Takes some time to update, each time you insert/update/delete a row

Which means you should only define indexes that are useful for your application's needs : too many indexes will slow down the writes more than you'll gain from the reads.

Answer 3

Having an index means INSERTs and UPDATEs take a bit longer. If you have too many indexes, then the benefit of faster search times can become not worth the extra INSERT and UPDATE time.

Answer 4

Yes. You should only add those indexes, that are necessary.

An index requires extra space, and, when inserting / updating / deleting records, the DBMS needs to update those indexes as well. So, this means that it takes more time to update/add/delete a record, since the DBMS has to do some extra administration.

adding numerous indexes to tables that I think may require one.

You should only add those indexes for which you're sure that they're necessary. To determine the columns where you could put indexes on, you could:

• add indexes to columns that are foreign keys
• add indexes to columns that are often used in where clauses
• add indexes to columns that are used in order by clauses.

Another -and perhaps better- approach, is to use SQL Profiler:

• use SQL Profiler to trace your application / database for a while
• save the trace results
• use the trace results in the Index Tuning Wizard, which will tell you which indexes you should create, what columns should be in each index, and it will also tell you the order of those columns for the index.

Answer 5

Yes; having an index makes selects faster but potentially makes inserts slower, as the indexes must be updated for each insert. If your application does a lot of writing and not much reading (e.g. an audit log) you should avoid extra indexing.

Answer 6

Indexes cause an increase in database size and the amount of time to insert/update/delete records. Try not to add them unless you know you will use them.

Answer 7

• update and insert cost more as indexes need to be updated as well
• more space used

Answer 8

Don't create any extra indices at the begining. Wait until you've at least partly developed the system so you can have an idea about the usage of the table. Generate query plans to see what gets queried (and how, and the performance costs) and THEN add new indices as needed.

Answer 9

Do not index blindly! Take a look at your data to see which columns are actually being used in SELECT predicates and index those.

Also consider that indexes take room. Sometimes a lot of room. I have seen databases where the indexing data far outweighed the raw data in sheer volume.

Answer 10

Extra space, Extra time to insert like everyone has said.

Also, you should be certain of your indexes and your design because sometimes indexes can actually slow down queries if the query optimizer chooses the wrong index. This is uncommon but can happen if by optimizing an index for a particular join and causing another join to actually become slower. I don't know about SQL Server but you'll find lots of tricks around for hinting the mySQL optimizer to build queries in specific ways to get around this.

DBA's get payed a lot of money to know about weird gotcha's like this with indexes(among other things) so yeah, there are downsides to adding lots of indexes so be careful. Lean heavily on your query profiler and don't just throw indexes blindly at problems.

Answer 11

Take a look at the columns used in your where clauses, look at columns used in joins if any. Generally the very simplest rule of thumb. Extraneous indexes as pointed out before will slow down your DML statements and are generally not recommended. Ideally, what I have done is finish the entire module and during your unit testing phase, ensure that you can do load analysis on the module and then check whether or not you are seeing slow downs and after analyzing where the slow downs are, add indexes.

Answer 12

I think it's been answered already, but basically indexes slow down inserts/updates as the index is updated when a new record is inserted (or an existing one updated).

Space is also a consideration, both memory and disk.

So for databases where a large amount of transactions are occurring, this will definitely have a noticeable performance impact (which is why performance tuning includes adding and removing indexes to optimize certain activities performed against the database).

Good luck