Indexes, why don't just index everything and when to use indexes?

Question

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.

Says our beloved MySQL manual.

In that case, why don't just index every column?

And since I have this feeling that it would be a bigger hit to performance, when should we use indexes/best practices for indexes?

Thanks in advance!

Answer 1

Creating an index always comes at a cost: The more indices you have on a table, the more expensive it is to modify that table (i.e. inserts, updates and deletes take longer).

In turn, queries that can use the indices will be faster. It's a classical tradeoff. On most tables a small number of commonly used indices is worth the cost, because queries happen often enough (or their performance is much more important than the modification performance).

On the other hand, if you have some kind of log table that is updated very often, but queried only very rarely (for example in case of a catastrophic failure), then adding an index would add a big cost and provide very little advantage.

Also: whether or not an index is useful depends a lot on the exact query to be executed. It's possible that you have indices spanning each column, but the query can't use it because the indices are in the wrong order, have the wrong information or the wrong format. So not all indices help all queries.