Answer to Question #122297 in Databases | SQL | Oracle | MS Access for Mawuko

Question

How to get the best out of an index?

Answer 1

Typically, MySQL can only use one index per table in a query. Therefore, it makes no sense to create more than one index for each query. It is preferable that the same indexes correspond to as many queries as possible, as this will reduce the load on the database when inserting or updating data (which also requires updating the indexes).

When creating an index, the most important parts are the equality conditions in the WHERE and JOIN conditions. In most cases, conditions such as name = ‘Alex’ will allow the database to filter out most of the rows from the table and go through a small number of rows to get the desired results. Therefore, we must start indexing by adding these columns to the index.

Then you should study the conditions of the range, but you should add only one of them - the most selective, since MySQL cannot handle a large number of them. In some cases, when there are no range conditions, it makes sense to add GROUP BY / ORDER BY columns, assuming that the ordering is performed in only one direction (ASC / DESC).

In some cases, it also makes sense to create a separate index that contains the columns of the ORDER BY clause, since MySQL sometimes chooses to use it. Note that for this, the index must contain all columns from the ORDER BY clause, and all of them must be specified in the same order (ASC / DESC). This does not guarantee that the database optimizer will select this index and not other composite indexes, but it is worth a try.

In addition, in some cases, it makes sense to also add columns from the SELECT clause to the index in order to have a full coverage index. This is relevant only if the index is not yet "too large." What is too big? Well, there is no official rule, but let's say ... 5-7 columns? Creating a covering index allows the database not only to filter using the index, but also to extract the information required by the SELECT clause directly from the index, which saves valuable I / O.