EXPLAIN’s rows column is an estimate, not a count

The rows column in an EXPLAIN is read as a measurement — “this query looked at 8,412 rows”. It is the optimiser’s guess, derived from index statistics, and on InnoDB those statistics are themselves resampled from a small number of randomly chosen index pages. The figure can be out by an order of magnitude on a table that has not been analysed since a bulk load.

EXPLAIN SELECT * FROM orders WHERE customer_id = 91G
          type: ref
           key: idx_customer
          rows: 8412

SELECT COUNT(*) FROM orders WHERE customer_id = 91;
-- 312

ANALYZE TABLE orders;

EXPLAIN SELECT * FROM orders WHERE customer_id = 91G
          rows: 340

This matters beyond reading the output, because the optimiser makes its plan out of the same guess. If it believes a condition matches a third of the table it will discard the index and scan, and a stale estimate is the usual explanation for a query that was fast for a year and became slow overnight with no code or schema change at all. ANALYZE TABLE recomputes the statistics and takes a brief read lock; on InnoDB the sampling depth is innodb_stats_sample_pages, which defaults to 8 and can be raised for tables whose plans keep flapping. Treat rows as an order of magnitude and nothing finer. When the real number is what you need, run the query with SQL_NO_CACHE and read Handler_read_rnd_next and friends out of SHOW SESSION STATUS before and after — that is counted rather than estimated, and it is the only figure worth quoting to anyone.