mysql too many indexes?

Question

I am spending some time optimizing our current database.

I am looking at indexes specifically.

There are a few questions:

• Is there such a thing as too many indexes?
• What will indexes speed up?
• What will indexes slow down?
• When is it a good idea to add an index?
• When is it a bad idea to add an index?
• Pro's and Con's of multiple indexes vs multi-column indexes?

Answer 1

What will indexes speed up?

Data retrieval -- SELECT statements.

What will indexes slow down?

Data manipulation -- INSERT, UPDATE, DELETE statements.

When is it a good idea to add an index?

If you feel you want to get better data retrieval performance.

When is it a bad idea to add an index?

On tables that will see heavy data manipulation -- insertion, updating...

Pro's and Con's of multiple indexes vs multi-column indexes?

Queries need to address the order of columns when dealing with a covering index (an index on more than one column), from left to right in index column definition. The column order in the statement doesn't matter, only that of columns 1, 2 and 3 - a statement needs have a reference to column 1 before the index can be used. If there's only a reference to column 2 or 3, the covering index for 1/2/3 could not be used.

In MySQL, only one index can be used per SELECT/statement in the query (subqueries/etc are seen as a separate statement). And there's a limit to the amount of space per table that MySQL allows. Additionally, running a function on an indexed column renders the index useless - IE:

WHERE DATE(datetime_column) = ...