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 field is never used, it is a bad index (if you feel unecessary things are bad.).

Answer 2

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 3

The index is to help us to search the rows faster.

If the index column is not used for searches there is no point in defining it.

If the values in that column keep changing very frequently it will be extra work for database server (for re-indexing)

If there are too many inserts and deletes from table it will be extra work for server

Answer 4

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.

Answer 5

I've linked to it before, and I'll link to it again, because it's excellent:

SQL Indexing in 9 Minutes and a Half, by Stephane Faroult.

Answer 6

There is an inherent performance hit from having indexes (creation and maintenance of the structure). You usually want that hit to get the benefit of faster scans. When you don't get the benefit it's just a net loss and that's a bad index.

Possible reasons:

• Never used indexes
• Redundant indexes
• Tables that aren't scanned very often but updated continuously (the hit of having the index outgrows the benefits, because the table is rarely scanned).
• Tables that are scanned often and updated continuously. In this case you can get both the benefit of an index and a snappy update/insert by having an indexless table for inserts/updates and a table with indexes for the scans that gets updated in daily or hourly batches (there are cases where this doesn't work, of course. Then you need to get better hardware or redesign the app if you get a serious performance problem in such a case).

How to find your bad indexes? Most RDBMSs have options to show the query plan, there you can see if the indexes you set up are being used in the way you expect them to. This leads me to a final advice, think about your indexes, never create one "just in case".

Answer 7

An index is bad if you never search on it. For example, an index (Col1, Col2, Col3) is a waste of resources if you never search with Col1, Col2 and Col3 in the same query.

Answer 8

One important thing to keep in mind with indexes (apart from the aforementioned "actual-use" part) is the notion of selectivity.

When constructing indexes, you want to create indexes on columns that have a good chance of "high selectivity". This requires some understanding of the data in the column (which you may or may not have depending on your knowledge of the domain / availability of sample data).

Selectivity = # of Distinct Values / Total # of Rows

Lets use a table "People" with columns for Given_name, Surname, Gender, Age

For example, creating an index on a column such as Gender (where gender is constrained to NULL, M or F) would not provide much benefit during a query (especially if the query already results in a table scan for other reasons). In any scenario, the selectivity of this index would be extremely low. Depending on the DBMS, using this index may actually be worse that a full table scan.

However, creating a composite index on ( Given_name , Surname ) would provide benefits when executing queries against those columns. The selectivity of this index (for most populations) would be pretty good.

An index with selectivity of 1 is the ideal, however, the only way to achieve a selectivity of 1 is to have a unique index on a non-nullable column.

Also, keep in mind that you can easily write queries to "keep track" of your indexes and their selectivity.