When is an index (in a DBMS) a bad index?

Question

Can anyone tell me when an index is a bad index?

Answer 1

If the indexed column is never searched on and the table is heavily updated you don't get the benefit of performance that indeces are for. In contrary you might suffer performance hit.

Answer 2

One circumstance under which an index is pretty much unconditionally bad is if there is another index which uses the same columns (in the same order) as a prefix:

CREATE INDEX ix_good ON SomeTable(Col1, Col2, Col3);
CREATE INDEX ix_bad  ON SomeTable(Col1, Col2);

The bad index is a waste of disk space and slows down modify operations to no benefit.