speed of inserts dependence on number of indices and size of the table

[Andre Abramenko]

Many people recommend that no more than 5 indices are ever created for the same table. Is it the number, or the combination of size of the table and the number of indices that is the determining factor in performance?

[A. Clay Stephenson · Accepted solution]

Hi Andre:

There is no magic answer to this question. There is always a tradeoff in that more indices will make selects faster (or at least almost all of the time) but that more indices will make inserts, deletes, and updates (if the keys are altered) slower. I certainly have cases where tables have many rows, many indices, and the rows themselves are quite large and yet performance is excellent. I certainly would not say that 5 is the limit.

I can tell you that in most B-trees, the thing to avoid is highly duplicate keys. For example, if you index state, then operations (especially deletes) on 'CA' are going to take a long time. Even CITY, STATE, ZIP could still be highly duplicate. In general, I've found the best compromise to be to create composite indices with the most used parts of the index as the most significcant part of the key. The optimizer is smart enough to use the 'front part' of the key and you can you the 'back parts' of the key to avoid excessive duplicate key generation. In a few cases, I've even tacked on a rowid just to avoid highly duplicate data.

The best way to determine where your problems lie is to plot the performance vs number of rows ,N, or better still plot the performance vs log N. The slope of the log plots can indicate the severity of your problem. For example, if you see performance degrading with a slope of 2, then you have an N-squared problem; a slope of 4 indicates an N^4 problem and you better come up with a new indexing scheme very quickly.

[Steve Lewis]

Can I just add to the above:
If the table data is relatively static, or you are dealing with a data warehouse, then you can have as many indexes as you like.
If the table is write-bound, then the fewer the better, depending on the application.
Avoid indexes on flags, instead partition the table, or create a look-up table containing the keys of the rows you may be interested in.

Whatever you choose, remember to update statistics when the population changes and, if you are using Oracle, clear the SQL statement cache so that the optimizer is updated.

[Andre Abramenko]

The table is going to have many reads, but quite a few writes as well. My guess is 10:1.

[Dennis J Robinson]

Large amount of indices will hurt insert performance.

In order to provide best update performance use rowid if possible.

[Deepak Extross]

While there is no hard and fast rule on the number of indexes per table, in most cases you should be able to limit it to 5 indexes. It's a bad idea to go overboard and create indexes on every column - just index the key ones, which are most likely to be queried on. Most important, queries should be intelligently written to make use of the indexes that you have. In many cases, its a simple matter of adding or rearranging the "where" clauses.