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

Answer 2

The EXPLAIN query will use the value provided in the INFORMATION_SCHEMA table, which contains merely a rough estimate of the row count for innodb tables - see notes section in mysql docs on INFORMATION_SCHAME.TABLES.