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.

Virgievirgil

possible duplicate of EXPLAIN and COUNT returning two different values – Vintager

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

Cablegram

You are right. But my condition is that the explain is small than COUNT() – Virgievirgil

And, i have Optimized the table. – Virgievirgil

Answer 2

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

Rianon

Answer 3

Execute ANALYZE TABLE table_name; - it will update statistics that EXPLAIN uses, and you'll get correct numbers. For example: when there is no data in table at all, EXPLAIN will suggest that this table is empty and optimize queries to filter first based on that table (as it doesn't read anything from disk, memory and so on). Then when data will be loaded if you don't execute ANALYZE TABLE table_name;, optimizer still suggests that table is still empty, and does not use an optimal execution plan for query. EXPLAIN behaves same way - it doesn't look for current count of rows in table, it looks for statistics generated by ANALYZE TABLE table name (that is executed automatically in some situations - 1/16 of count of rows in table changed for example).

Gadmann

Answer 4

The question of the OP is valid, but judging from the answers, I think there is a misunderstanding of what the rows column of explain is actually telling us.

the mysql documentation for explain rows states:

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

So what COUNT(*) tells you and what Explain rows tells you are two different things that MAY happen to result in the same number, but they are not the same information.

The first is a count of all the rows that match the query being run. The second, is an estimate of all the rows that need to be examined for the query to run.

So when i run

SELECT COUNT(id) FROM table WHERE user_id = 1

i get the number of rows where user_id = 1.

When i run

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

the rows column contains all the rows that mysql needs to go through to give you that answer. That's the whole table in this case.

Hammer