Inaccurate row count in MySQL EXPLAIN statement

Question

I have a large table of contacts and I want to run a COUNT query on it, filtering by company_id. I also want to see if EXPLAIN gives a semi-approximate count, so that I can use that value instead of running an actual COUNT query.

So, I tried doing this

mysql> select count(*) from contacts where company_id=60;
+----------+
| count(*) |
+----------+
| 23475317 |
+----------+
1 row in set (14.55 sec)

mysql> explain select count(*) from contacts where company_id=60;
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys           | key                     | key_len | ref   | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+----------+----------+-------------+
|  1 | SIMPLE      | contacts | NULL       | ref  | contacts_company_id_idx | contacts_company_id_idx | 5       | const | 24347253 |   100.00 | Using index |
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+----------+----------+-------------+
1 row in set, 1 warning (0.25 sec)

The above is expected, EXPLAIN is giving a rough count, and the below is expected as well, where for very small counts, EXPLAIN gives the exact count.

mysql> select count(*) from contacts where company_id=1;
+----------+
| count(*) |
+----------+
|      181 |
+----------+
1 row in set (0.37 sec)

mysql> explain select count(*) from contacts where company_id=1;
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | contacts | NULL       | ref  | contacts_company_id_idx | contacts_company_id_idx | 5       | const |  181 |   100.00 | Using index |
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.31 sec)

But what I don't understand is why EXPLAIN fails when there are no rows returned.

mysql> select count(*) from contacts where company_id=2;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.34 sec)

mysql> explain select count(*) from contacts where company_id=2;
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | contacts | NULL       | ref  | contacts_company_id_idx | contacts_company_id_idx | 5       | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.25 sec)

How can EXPLAIN return a row count of 1 when there are no rows at all?

Answer 1

Maybe this can explain why :

It is showing how many rows it ran through to get your result. The reason for the wrong data is that EXPLAIN is not accurate, it makes guesses about your data based on information stored about your table.