A question about indexes regarding to the gain of inserts & updates in database

Question

Hi,

I’m having a question about the fine line between the gain of an index to a table there is growing steadily in size every month and the gain of queries with an index.

The situation is, that I’ve two tables, Table1 and Table2. Each table grows slowly but regularly each month (with about 100 new rows for Table1 and a couple of rows for Table2).

My concrete question is whether to have an index or to drop it. I’ve made some measurement that an covering index on Table2 improve my SELECT queries and some rather much but again, I’ve to consider the pros and cons but having a really hard time to decide.

For Table1 it might not be necessary to have an index because the SELECT queries there is not that common.

I would appreciate any suggestion, tips or just good advice to what is a good solution. By the way, I’m using IBM DB2 version 9.7 as my Database system

Sincerely

Mestika

Answer 1

Any additional index will make your inserts slower and your queries faster.

To take a smart decision, you will have to measure exactly by how much, with the amount of data that you expect to see. If you have multiple clients accessing the database at the same time, it may make sense to write a small multithreaded application that simulates the maximum load, both for inserts and for queries.

Your results will depend on the nature of your data and on the hardware that you are running. If you want to know the best answer for your usecase, there is no way around testin accurately yourself with your data and your hardware.

Then you will have to ask yourself:

Which query performance do I need?
If the query performance is good enough without the index anyway, easy: Don't add the index!

Which insert performance do I need?
Can it drop below the needed limit with the additional index? If not, easy: Add the index!

If you discover that you absolutely need the index for query performance and you can't get the required insert performance with the index, you may need to buy better hardware. Solid state discs can do wonders for database servers and they are getting affordable.

If your system is running fine for everyone anyway, worry less, let it run as is.