Why MySQL pick a different index for the same query structure but different values?

Right now i have some issues understanding how MySQL picks the index to use on a query. I am executing a “COUNT QUERY”, but everytime i use a different foreign key for the query, MySQL picks a different index to use.

Let me elaborate and explain my situation. On our DB table we have around 40 millions rows. I am using two “Count querys” with range filters to get the number of rows. The two clients i use to test this behavior have this values:

  • Cliente_id 1 has around 8 millions rows on reports table.
  • Cliente_id 2 has around 1.5 millions rows on reports table.

Now i execute the following querys:

Count query of client with id 1 (finish in 5.6 seg)

Select count(*) From reports where cliente_id = 1 and (created_at  >= '2020-01-01T00:00:00' and created_at <= '2020-01-31T00:00:00')

Count query of client with id 2 (finish in 58 seg)

Select count(*) From reports where cliente_id = 2 and (created_at  >= '2020-01-01T00:00:00' and created_at <= '2020-01-31T00:00:00')

As you can see the second query of the client with less records, takes too long to execute. This seems odd, so when i try to use “EXPLAIN” to see which index is been use i got this:

EXPLAIN OF query from cliente_id 1 (Use created_at index.)

1   SIMPLE  reports range   index_reports_clienteid,index_reports_created_at    index_reports_created_at    63      5534206 Using index condition; Using where

EXPLAIN OF query from cliente_id 2 (Use cliente_id index.)

1   SIMPLE  reports range   index_reports_clienteid,index_reports_created_at    index_reports_clienteid 63      5534206 Using index condition; Using where

The second Count query is using a different INDEX. I been reading a lot of Mysql doc, but with not luck. After a while i found a way to force the use of a specific index on a Query. I try this:

Selec Count with Use Index

SELECT COUNT(*) FROM reports USE INDEX(index_reports_created_at)  WHERE (company_id = '2' AND (created_at >= '2020-01-01T00:00:00' AND created_at <= '2020-01-31T0:0:00' ))

When i force the use of index on the query it only took around 6 seconds to finish. In theory the most optimal index to use is created_at, but on this situation MySQL picks “cliente_id index”.

What is the criteria here to pick cliente_id over created_at? I just don’t understand this behavior. I will really appreciated if someone can help me with this situation.

MYSQL VERSION is 5.7.32 and i am using innoDB engine

NOTE. I can hardcode the use index on my querys but i dont think is the right think to do. So i will really like to avoid use “USE INDEX” on my querys.

Note2. Sorry for my poor english. I try my best to explain.

Thanks for the help.