Indexes, why not just index everything and when to use indexes?

Question

Indexes are used to quickly find rows with specific column values. Without an index, MySQL should start on the first row and then read the entire table to find the corresponding rows.

Says our favorite MySQL guide .

In this case, why not just index each column?

And since I have the feeling that this will be a big hit on performance , when should we use indexes / best practices for indexes ?

Thanks in advance!

Answer 1

Creating an index always comes at the expense of: the more indexes you have in the table, the more expensive it is to change this table (i.e. inserting, updating, and deleting takes longer).

In turn, queries that can use indexes will be faster. This is a classic compromise. In most tables, a small number of commonly used indexes are costly because queries occur quite often (or their performance is much more important than modification performance).

On the other hand, if you have some kind of log table that is updated very often, but is only requested very rarely (for example, in the event of a catastrophic failure), then adding an index would add a lot of value and provide a very small advantage.

Also: regardless of whether the index is useful, a lot depends on the exact query that needs to be executed. You may have indexes spanning each column, but the query cannot use it because the indexes are in the wrong order, have the wrong information, or the wrong format. Therefore, not all indexes help all queries.

Answer 2

By your logic, you would not index only each column, but each permutation of each column. The overhead associated with storing this information and maintaining its relevance would be extremely extensive.

Answer 3

An index is usually useful if it has good selectivity, that is, when the query selects a small fraction of the data based on the value (or range) of the indexed attribute.

Indexes are also good for joins when sorting rows by join attribute in both joined tables allows you to match rows and retrieve data in a single pass.

As already mentioned, indexes slow down updates and take up some memory (which in itself also slows down performance)