When a query is issued, one of the steps in the processing is called the Optimizer. Its job is to decide how to use the indeces present. If you search on a single criterion, then that one index will be used in a direct lookup. If you search on multiple criteria, and each has an index, then the optimizer will decide how to go about it. Usually, it will work through index hashing, and do a good job. So, from the searching end, the answer is that you can never have too many indeces. However, from the record insertion end, each added index costs you added time in doing the insertion. If there are far more insertions than searches, then don’t index. Otherwise, index the heck out of it. Note that some indeces are faster than others. If the data can be encoded numerically, then that index will be faster. A good rule of thumb is that the “cost” of an index (in a search) is roughly equivalent to the number of bytes in the data item. For DB2, here’s the list:
VARCHAR(n): 2n (note the exception to the timing rule)
VARGRAPHIC(n): 3n (note the exception to the timing rule)
DECIMAL(n,m): n/2, rounded up
Hashed indeces are faster when there is a large data item, but <10,000 distinct data values. UNIQUE indeces are faster than UNIQUE WHEN NOT NULL indeces, and UNIQUE WHEN NOT NULL indeces are faster than non-unique indeces for searching, but using the UNIQUE keyword costs time in insert.
If you’re using Oracle, the bad news is that the optimizer isn’t as good, but the good news is that you can drop hints. There’s a whole manual section on writing effective hints in Oracle.
Sheldon Linker (email@example.com)
Linker Systems, Inc. (http://www.linkersystems.com)