General
cancel
Showing results for
Did you mean:

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

SOLVED
Go to solution
Occasional Visitor

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

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?
5 REPLIES
Acclaimed Contributor
Solution

## Re: speed of inserts dependence on number of indices and size of the table

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.

If it ain't broke, I can fix that.
Honored Contributor

## Re: speed of inserts dependence on number of indices and size of the table

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.

Occasional Visitor

## Re: speed of inserts dependence on number of indices and size of the table

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