5

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?
rubo77
  • 816
  • 2
  • 13
  • 24

1 Answers1

5
  • 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.

ETL
  • 988
  • 6
  • 18
  • 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 00: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 05: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 08: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
  • So from what I read here, my advice to my friend, that put indexes on all his database columns in all tables, would be that this is still the better solution than don't having indexes at all. Is this correct? (sure, I know the best solution would be he puts only the needed indexes, but he doesn't want to learn that) – rubo77 Jul 23 '14 at 13:11
  • "Better solution" is relative. If he is willing to degrade inserts and updates and have a longer query pre-processing time, sure, go ahead and add indexes on all columns. – ETL Jul 23 '14 at 15:19