[ACCEPTED]-mysql too many indexes?-indexing

Accepted answer
Score: 161

What will indexes speed up?

Data retrieval 25 -- SELECT statements.

What will indexes slow 24 down?

Data manipulation -- INSERT, UPDATE, DELETE 23 statements.

When is it a good idea to add 22 an index?

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

When is it a 20 bad idea to add an index?

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

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

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

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

WHERE DATE(datetime_column) = ...
Score: 72

I disagree with some of the answers on this 44 question.

Is there such a thing as too many 43 indexes?

Of course. Don't create indexes 42 that aren't used by any of your queries. Don't 41 create redundant indexes. Use tools like 40 pt-duplicate-key-checker and pt-index-usage to help you discover the indexes you 39 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 38 answers have advised that INSERT/UPDATE/DELETE 37 are slower the more indexes you have. That's 36 true, but consider that many uses of UPDATE 35 and DELETE also have WHERE clauses and in 34 MySQL, UPDATE and DELETE support JOINs too. Indexes 33 may benefit these queries more than making 32 up for the overhead of updating indexes.

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

When is it a good idea 22 to add an index?

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

When is it a bad 19 idea to add an index?

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

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

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

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

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

See 1 also my presentation How to Design Indexes, Really.

Score: 7

Is there such a thing as too many indexes?

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

What will indexes speed up?

SELECTs.

What 11 will indexes slow down?

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

When is it 9 a good idea to add an index?

When your application 8 needs another WHERE clause.

When is it a 7 bad idea to add an index?

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

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

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

Score: 4

Is there such a thing as too many indexes?

Yes. Don't 17 go out looking to create indexes, create them 16 as necessary.

What will indexes speed up?

Any 15 queries against the indexes table/view.

What 14 will indexes slow down?

Any INSERT statements 13 against the indexed table will be slowed 12 down, because each new record will need 11 to be indexed.

When is it a good idea to 10 add an index?

When a query is not running 9 at an acceptable speed. You may be filtering 8 on records that are not part of the clustered 7 PK, in which case you should add indexes 6 based on the filters you are searching upon 5 (if the performance deems fit).

When is it 4 a bad idea to add an index?

When you do it 3 for the sake of it - i.e over-optimization.

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

Depends 1 on the queries you are trying to improve.

Score: 3

Is there such a thing as too many indexes?

Yup, like 7 all things, too many indexes will slow down 6 data manipulation.

When is it a good idea 5 to add an index?

A good idea to add an index 4 is when your queries are too slow (i.e. you 3 have too many joins in your queries). You 2 should use this optimization only after 1 you built a solid model, to tweak the performance.

More Related questions