Database indexes: A good thing, a bad thing, or a waste of time?

Question

Adding indexes is often suggested here as a remedy for performance problems.

(I'm talking about reading & querying ONLY, we all know indexes can make writing slower).

I have tried this remedy many times, over many years, both on DB2 and MSSQL, and the result were invariably disappointing.

My finding has been that no matter how 'obvious' it was that an index would make things better, it turned out that the query optimiser was smarter, and my cleverly-chosen index almost always made things worse.

I should point out that my experiences relate mostly to small tables (<100'000 rows).

Can anyone provide some down-to-earth guidelines on choices for indexing?

The correct answer would be a list of recommendations something like:

• Never/always index a table with less than/more than NNNN records
• Never/always consider indexes on multi-field keys
• Never/always use clustered indexes
• Never/always use more than NNN indexes on a single table
• Never/always add an index when [some magic condition I'm dying to learn about]

Ideally, the answer will give some instructive examples.

Answer 1

Indexes are kind of like chemotherapy...too much and it kills you...too little and you die...do it the wrong way and you die. You gotta know just how much, how often, and what kind to make it not kill you.

Your hardware, platform, environment, load all play a role. So to answer your questions..

Yes, possibly sometimes.

Answer 2

As a rule of thumb, primary keys and foreign keys need to be indexed. Usually primary key are indexed just by defining them as such, but FKs are not in every database (they definitely are not in SQL Server, I can't really speak for other dbs). You will be using these in joins, so it is generally critical to performance to define these.

Now if you have fields you often use in where clauses, they can benefit from indexes as well providing several things:

• First the field must have a range of values. A bit field or a field with only 2 or 3 values will almost never use an index.

• Second the queries you write must be sargable. That is they must be designed to use indexes. I suspect if you never get performance improvements from what look like likely candidates for indexes, then you probably have queries that are not sargable. For instance take "WHERE Name like '%Smith'" as a where clause. Without knowing the first characters, the optimizer can't use the index.

Small tables rarely benefit much from indexes. If the optimizer can hold the whole thing in memory, then it is often faster to do so. If you were working with multimillion record tables, you would see that indexes are critical.

Indexing can be very complex and if you are interested in the subject, I suggest you get a good book on performance tuning your particular database and read in depth about them.