How to Interpret the rows Value in MySQL EXPLAIN Output

MySQL, like most relational databases, displays a rows column in the EXPLAIN output, which represents the optimizer's estimate of how many records will be scanned based on table and index statistics.

How to judge SQL performance from the rows value? The article outlines three typical cases.

Rows value small – performance high

When the same query uses different indexes, a smaller rows value usually means better performance.

Example SQL:

select * from t1 where r1=2 and r2=2

Possible indexes:

idx_r1(r1) / idx_r2(r2) / idx_u1(r1,r2) / idx_u2(r2,r1)

Using force index to force each index, the EXPLAIN results show the rows values:

localhost:ytt>desc select * from t1 force index(idx_r1) where r1=2 and r2=2\G
... rows: 18638

localhost:ytt>desc select * from t1 force index(idx_r2) where r1=2 and r2=2\G
... rows: 102

localhost:ytt>desc select * from t1 force index(idx_u1) where r1=2 and r2=2\G
... rows: 12

localhost:ytt>desc select * from t1 force index(idx_u2) where r1=2 and r2=2\G
... rows: 12

The joint indexes idx_u1 and idx_u2 produce the smallest rows count and thus the highest efficiency.

Rows value small – performance not guaranteed

For range queries, a small rows value does not always mean the best plan.

Example SQL:

select * from t1 where r1<5

Two plans are compared: a full table scan and an index range scan.

Plan 1 (full scan): rows: 101745, filtered: 50.00
Plan 2 (force index idx_r1): rows: 50872, filtered: 100.00

Although Plan 2 has a lower rows value, the optimizer still chooses Plan 1 because its overall cost is lower. EXPLAIN ANALYZE confirms that the full scan runs faster.

Rows value unsuitable for judgment (multi‑table joins)

In join queries, the rows column can be misleading.

Example SQL:

select a.* from t1 a join t2 b using(f0,f1)

Initial EXPLAIN shows t1 as the driving table with rows ≈ 101 745 (full scan) and t2 as the inner table with rows = 1.

After collecting fresh statistics ( analyze table t1, t2 ) and re‑optimizing, the plan swaps the tables: t2 becomes the driving table with rows ≈ 48 339, and t1 becomes the inner table with rows = 1, reducing the overall rows count and improving performance.

These examples illustrate that the rows value must be interpreted together with the optimizer's cost model, table statistics, index statistics, and data distribution; it cannot be used in isolation to decide whether a query is efficient.