Why do the rows in explain seem to be doubled?

Question

MySQL

query: this table has 4.5 million records, there is an index on topic_id. The query returns about 128k records. If you do EXPLAIN, then it is shown that the index is taken, but in the rows column it is shown that it bypassed 233k rows. How can this be? For example, if you take a different topic_id, then there will be a case when 37 records will return, and in explain in rows there will also be 37. And in cases where more than 10k records start, it supposedly bypasses twice. What is it connected with? + additional question, this table has a composite index on topic_id and another column, and there is an index on topic_id. In this case, it takes for some reason a composite index. Shouldn't it take the index that is thrown on the topic_id? Yes, if you specify FORCE INDEX on a single index, then it shows that it captures even more rows
SELECT * FROM messages WHERE topic_id = 62365;


MySQL version 8
InnoDB

Answer 1

The values rows​​in the EXPLAIN output are, by definition, imprecise - an estimate based on statistics.
If mysql is fresh enough, you can do EXPLAIN ANALYZE - then the query will actually be executed and show the real values ​​for rows.
If EXPLAIN is very wrong with estimates, it is worth doing ANALYZE TABLE and maybe playing around with statistics settings for the table (UPDATE HISTOGRAM)