MySQL Unnecessary Indexes

Question

If I have this index:

(col1, col2, col3)

I know it helps when I search through (col1); (col1, col2); (col1, col2, col3).

If I create another index with the exactly same columns, phpMyAdmin will warn me that one of those indexes may be removed, because they are the same.

However, if I have these indexes:

(col1, col2, col3)

(col1, col2)

(col1)

phpMyAdmin won't warn me at all.

So my question is, are the last two indexes necessary in any case? I think only the first index is enough.

Thank you.

We don't know if they're necessary w/o seeing the table structure. If they are unique key or primary key or whatever, you can't delete them. Need more info to answer the 2nd part of the question. SHOW CREATE TABLE tablename\G — randomx
– randomx, Commented Aug 16, 2012 at 21:14
@randymelder - I'm not talking about UNIQUE or PRIMARY keys. I'm talking about common columns. I know that those you referred need to exist. Thank you. — Nuno
– Nuno, Commented Aug 16, 2012 at 21:17

randomx · Accepted Answer · 2012-08-16 21:09:15Z

3

MySQL will only use one index (the leftmost) to optimize the search. Quoting from the documentation:

"If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3)."

However, if any of the indexes are UNIQUE, then there's probably a good reason for them to be there.

If during your analysis you find that any of the columns are frequently used apart from one another, then you should consider adding separate indexes for each to optimize those queries.

E.g.

ALTER TABLE tablename ADD INDEX (col1), ADD INDEX (col2), ADD INDEX (col3);

edited Aug 16, 2012 at 21:09

answered Aug 16, 2012 at 21:06

randomx

2,3771 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nuno Over a year ago

From your answer, I assume my point on the question is correct then. I know that I may have to create different indexes, where the leftmost prefix is different. The same for UNIQUE and PRIMARY. Thank you for your answer.

Collectives™ on Stack Overflow

MySQL Unnecessary Indexes

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related