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?
  • Do you mean index on all tables as per title or on all columns as per question?
    – mmmmmm
    Apr 14, 2013 at 21:57

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.

  • ok, so only the question left, if the planner needs more time
    – rubo77
    Apr 14, 2013 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, 2013 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. Apr 15, 2013 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, 2013 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, 2013 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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