-2

By indexing a column, MySQL does not need to look through every row in the table, instead it can find the data you are searching for alphabetically, then skip immediately to look at the row(s) where the data is located.

So I'm starting to think that, by setting every columns the performance should be even better. Am I right?

And if so, what would be the downside of this? Because if it's better for performance and if there is no downside, every columns should be defined as index by default.

Thanks for your advices.

Zag zag..
  • 6,041
  • 6
  • 27
  • 36
  • Downside is "just", that MySQL has to maintain those indexes at every `INSERT,` `UPDATE` etc. If your data changes a lot, this can be quite costly. – Sirko Feb 20 '15 at 18:50
  • possible duplicate of [MySQL - why not index every field?](http://stackoverflow.com/questions/5446124/mysql-why-not-index-every-field) – Marcus Adams Feb 20 '15 at 19:13

1 Answers1

1

Large numbers of indexes can slow down INSERT/UPDATE queries and take up significant amounts of disk space (potentially more than the data itself). You should index the columns intelligently, based on the sorts of queries your application makes.

ceejayoz
  • 176,543
  • 40
  • 303
  • 368
  • Yes fully agree. Link your columns based on the 'sparsity' of the column. Basically; the more vastly different values of the columns are the more you could need an index. – Sweet Chilly Philly Apr 13 '18 at 03:10