3

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.

3 Answers 3

3

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.

2
  • Do you know what to do if row count is order of magnitude higher? I have a query which uses correct index but row estimate is around 20 million and result set is only 2000.
    – moger777
    Apr 21, 2020 at 15:30
  • @moger777 are there any other conditions in the query? Order of rows to check from (even the best) index and number of rows in results do not need to correspond strongly. Otherwise ANALYZE TABLE might help set that straight if it is really an error in the stats.
    – jkavalik
    Apr 21, 2020 at 22:51
1

In your query if Payment.fordate is not indexed, the optimizer expects to search the whole table. Because you are returning only the count(*) value only one row will be returned. Adding an additional column and a group by clause could alter the expected number of rows returned.

Explain plans use estimated data based on statistics available from the database. This data is usually wrong, but close enough to build a reasonable plan. Normally, orders of magnitude count, not actual values.

It is possible that the value is off by a factor of 2 due to an error in constructing the count for the between clause.

-1

The explain plan is simply showing you how many rows it had to sort through, to get your result.

If you were to remove your where clause and do a "select count (*) from [table_name]", then it would return a count of all rows in the table, and your explain plan would match the total.

1
  • 1
    The EXPLAIN shows how many rows it expects to visit. It does not actually do any "work" so cannot know the real numbers.
    – jkavalik
    Dec 11, 2015 at 6:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.