Why Does 'explain selected *' Row Count Differ from 'count()'?

Deviation in Row Counts between "count()" and "explain selected *""

The "explain selected *" command in MySQL provides an estimate of the rows processed during a query. However, this estimate can differ from the actual row count obtained using the "count()" function.

Explanation

The "explain selected *" statement displays information on how MySQL executes a query. Among the details provided is an estimate of the number of rows that the query will scan. This estimate is based on statistics maintained by MySQL about the table being queried.

However, these statistics may not always be accurate. Factors such as data distribution, table structure, and query context can influence the accuracy of the estimate. As a result, the "explain selected *" row count may not match the actual row count that the query processes.

Example

Consider the following example:

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 | 
+----+-------------+---------+-

As observed, the "explain selected *" statement estimates that 1,274,785 rows will be scanned to retrieve the data. However, the "count()" function returns an actual count of 1,291,958 rows.

Implications

The discrepancy between the row counts can mislead users into believing that an index is being used to optimize the query. However, this may not be the case, and the query could still be scanning a significant number of rows.

Conclusion

While "explain selected *" provides valuable information about query execution, it should not be relied upon as a precise measure of the actual row count processed by a query. The "count()" function should be used to obtain an accurate count of the matching rows.

The above is the detailed content of Why Does 'explain selected *' Row Count Differ from 'count()'?. For more information, please follow other related articles on the PHP Chinese website!