MYSQL queries doing Table Scan even using index key

Question

I started recently to study the performance of the queries in MYSQL, I am trying to use EXPLAIN to go analyzing and improving the indexes of my tables, until I came across the following situation.

I am making a simple query by filtering the users of the domain through the user field_domain, which is a key index.

When I use value 1 to select users belonging to domain 1, it seems that it doesn’t use index and scans all 9 records in the table:

Here, I use the value 2 to select users belonging to domain 2, and in this case, it seems that the index works correctly and selects the records directly.

I noticed this same behavior in some other queries, and a table scan cannot occur in any way because the project is a Saas Multi-tenant, which can lead to extremely time-consuming queries. Does anyone know what it might be?

Answer 1

Well, I’m still trying to understand why some queries aren’t using the index key. I believe it will still take a while to analyze everything better, however, I update the response.

First thing I tried was to force the use of the index with USE INDEX(), but the result was the same, the index was not used.

Then it dawned on me that there might be something wrong with the early records, which were old and registered a long time ago. I decided to delete the DB and insert it again. I made 3 backups of DB, one with the data and tables, one removing the old data and inserting new data and tables and the third with only the tables, without the data. In the 3 restorations the query continued not to use the index key.

I noticed that in some other queries the index key was also not used, and continued searching until I got to the following topic: Indexes in Mysql queries

At the end of the Maniero says:

In fact, it may be that the index is not used even if it has a suitable key. There are small volumes of data that the direct search in the data has a lower cost. The optimizer can identify that even if it can select sub-bands, subsequent analysis can be so costly that eliminating the index query can bring a better result. But in these cases the optimizer has renounced its use in a "conscious" way for its benefit. It can not be bad. Although there are cases that it errs.

I believe that may be what is happening to me, because the number of tuples are small and there are not many items to be filtered, the query ends up giving up the index key on some occasions, but not at all.