Why the rows returns by "explain" is not equal to count()?

Question

    mysql> select count(*) from table where relation_title='xxxxxxxxx';
+----------+
| count(*) |
+----------+
|  1291958 |
+----------+

mysql> explain select *  from table where relation_title='xxxxxxxxx';
+----+-------------+---------+-
| id | select_type | rows    |
+----+-------------+---------+-
|  1 | SIMPLE      | 1274785 | 
+----+-------------+---------+-

I think that "explain select * from table where relation_title='xxxxxxxxx';" returns the rows of relation_title='xxxxxxxxx' by index. But it's small than the true num.

Answer 1

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.

This is very useful information, for example when doing JOINS on many tables and you want to be sure that you aren't running through the entire joined table for one row of information for each row you have.

Here's a test on a 608 row table.

SELECT COUNT(id) FROM table WHERE user_id = 1

Result:

COUNT(id)
512

And here's the explain

EXPLAIN SELECT COUNT(id) FROM table WHERE user_id = 1

Result:

id  rows
1   608