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

开发者_Go百科

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!

---

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.

---

By your logic, you wouldn't index just every column, but every permutation of every column. The overhead involved in storing this information, and in keeping it up to date, would be utterly vast.

---

Generally index is helpful if it has a good selectivity, i.e. when the query selects a little portion of data based on the value (or range) of indexed attribute.

Also indice are good for merge joins, when sorting rows by a joining attribute in both joined tables allows to match rows and retrieve data in one pass.

As it was already mentioned, indexes slow down updates and take up some memory (which, by itself, slows down performance as well)