Indexes considered harmful

The knee-jerk reaction to poor query performance is 'Create an index!', but this is not always guaranteed to improve query performance, and in some cases can even damage it. Improving query performance can be a black art, and there are times when a query cannot be improved, despite your best efforts.

Indexes may be used by a query, but there's no guarantee about it. The query optimiser can only use a single index per table and has to decide which index will be most effective, so adding a new one doesn't necessarily mean that the optimiser will use it.

The more indexes you have, the slower your queries may run. For SELECT statements, the query optimiser will have to consider more indexes to decide which one is most suitable. For data modification statements, indexes will need to be rebuilt after each statement, so the more indexes you have the more work (both CPU and I/O) is needed to keep this data up-to-date. Data modification will also invalidate the index blocks being held in the key buffer, creating more CPU work to identify and clear the key buffer blocks and disk I/O as they're loaded back in by queries.

Indexes also take up disk space, and although disk space is (comparatively) cheap nowadays this could still push up storage requirements. Don't forget to factor in the disk I/O needed to read and write these indexes as well - although smaller than reading the data files, it will still 'steal' the available I/O bandwidth from data retrieval.

Before creating a new index, check which indexes have already been created, and determine how the table is being used - it may be that there are alternative ways to improve query performance without creating a new index. A new (or existing) multiple column index could be (re)defined, for example, and using aggregated data or splitting the table may also be an option. Recreate the table (and the data - the table contents are just as important as, if not more than, the structure) on another machine, create the new index and check that your queries actually use it - if it does improve performance, only then do it on your main server.

Used correctly, indexes can make a massive difference on query performance, but they are neither guaranteed nor without cost. Consider your options, and use indexes wisely.