Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know, that it is not a good design to set too many indexes, but just for understanding the theory:
What exactly are the disadvantages if you add an index on all columns in a table in MySQL?

  • Are the queries getting slower if I have indexes on columns, that i rarely use?
  • Are the inserts somewhat slower?
  • Is MySQL able to pick out the best index that speeds up the query?
share|improve this question
    
Do you mean index on all tables as per title or on all columns as per question? –  Mark Apr 14 '13 at 21:57
    
so why the downvote? if it was, cause i said "field" before, you can reverse the downvote now. and you can delete your comments about it –  rubo77 Apr 15 '13 at 1:27
1  
We've no way of know who downvoted or whether they'll ever look back and see the edit so it's probably better just to ignore it. I think it is a useful question in that it'll probably be asked again so it gets +1 from me. –  Jack Douglas Apr 15 '13 at 5:11
    
Yes, it'a a valid question. –  ypercube Apr 15 '13 at 5:24

1 Answer 1

  • Insert/Updates/Delete speed will degrade. The index has to be updated on data modification.
  • I don't know mysql in fine details but it also means more time spent by the query planner to determine the indexes to use.
  • More disk space.

These are the key disadvantages.

share|improve this answer
    
ok, so only the question left, if the planner needs more time –  rubo77 Apr 14 '13 at 23:08
    
Well it will mean more time for the planner, it may not be a significant amount of time, it depends on your database, your server, how many other things it need to do. I.e. you may or may not see a difference speed wise. –  ETL Apr 15 '13 at 0:03
1  
@rubo77 it's not just a speed issue, greater complexity also means the planner is more likely to choose a poor plan. Good indexing makes the planner's job easier. –  Jack Douglas Apr 15 '13 at 5:12
    
For example: if the table has just 5 varchar(255) columns and 4 million entries, would it slow down the planner significantly then, if there were indexes on all columns, compared to only indexes on the columns that are used in the query? –  rubo77 Apr 15 '13 at 8:11
    
The short answer is: yes it will. Just like doing 1+1+1 is slower than doing 2+1. It's one more operation thus it's "slower". The longer answer is: it depends. Do you have other tables in your database or that's the only one? Are you using prepared statements or not? How fast is the server? In summary, you probably won't even notice the difference. But yes, it will be slower, maybe 0.001ms slower in this case. –  ETL Apr 15 '13 at 13:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.