mysql too many indexes?

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?

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) = ...

I disagree with some of the answers on this question.

Is there such a thing as too many indexes?

Of course. Don't create indexes that aren't used by any of your queries. Don't create redundant indexes. Use tools like pt-duplicate-key-checker and pt-index-usage to help you discover the indexes you don't need.

What will indexes speed up?

• Search conditions in the WHERE clause.
• Join conditions.
• Some cases of ORDER BY.
• Some cases of GROUP BY.
• UNIQUE constraints.
• FOREIGN KEY constraints.
• FULLTEXT search.

Other answers have advised that INSERT/UPDATE/DELETE are slower the more indexes you have. That's true, but consider that many uses of UPDATE and DELETE also have WHERE clauses and in MySQL, UPDATE and DELETE support JOINs too. Indexes may benefit these queries more than making up for the overhead of updating indexes.

Also, InnoDB locks rows affected by an UPDATE or DELETE. They call this row-level locking, but it's really index-level locking. If there's no index to narrow down the search, InnoDB has to lock a lot more rows than the specific row you're changing. It can even lock all the rows in the table. These locks block changes made by other clients, even if they don't logically conflict.

When is it a good idea to add an index?

If you know you need to run a query that would benefit from an index in one of the above cases.

When is it a bad idea to add an index?

If the index is a left-prefix of another existing index, or the index doesn't help any of the queries you need to run.

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

In some cases, MySQL can perform index-merge optimization, and either union or intersect the results from independent index searches. But it gives better performance to define a single index so the index-merge doesn't need to be done.

For one of my consulting customers, I defined a multi-column index on a many-to-many table where there was no index, and improved their join query by a factor of 94 million!

Designing the right indexes is a complex process, based on the queries you need to optimize. You shouldn't make broad rules like "index everything" or "index nothing to avoid slowing down updates."

See also my presentation How to Design Indexes, Really.

Is there such a thing as too many indexes?

Indexes should be informed by the problem at hand: the tables, the queries your application will run, etc.

What will indexes speed up?

SELECTs.

What will indexes slow down?

INSERTs will be slower, because you have to update the index.

When is it a good idea to add an index?

When your application needs another WHERE clause.

When is it a bad idea to add an index?

When you don't need it to query or enforce uniqueness constraints.

Pros and Cons of multiple indexes vs multi-column indexes?

I don't understand the question. If you have a uniqueness constraint that includes multiple columns, by all means model it as such.