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

Solution 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

Solution 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.

Solution 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).