Mysql – why in explain in thesql row count is very high

MySQLmysql-5.5mysql-5.6

i am using mysql, here is query-

mysql> select count(*) from payments as Payment WHERE `Payment`.`fordate` BETWEEN '2015-11-28 00:00:00' AND '2015-12-31 23:59:59';
+----------+
| count(*) |
+----------+
|   187216 |
+----------+
1 row in set (0.16 sec)

mysql> explain select count(*) from payments as Payment WHERE `Payment`.`fordate` BETWEEN '2015-11-28 00:00:00' AND '2015-12-31 23:59:59';
+----+-------------+---------+-------+-----------------+-----------------+---------+------+--------+--------------------------+
| id | select_type | table   | type  | possible_keys   | key             | key_len | ref  | rows   | Extra                    |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | Payment | range | pafordate_index | pafordate_index | 5       | NULL | 379894 | Using where; Using index |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+--------+--------------------------+
1 row in set (0.04 sec)

why in explain row count is too much but in actual it is less.

Best Answer

Is the table InnoDB? For that the index statistics are gathered by "index dives" and are by definition imprecise.

EXPLAIN is not actually executing the query, but uses the estimates to plan it so it shows those imprecise numbers.

The important thing is that if it keeps numbers in the same order as reality (which, being a double in your case, it is), it works well enough for most cases.

Keeping exact numbers is too cumbersome and not even really possible in transactional environment.

Related Question