rows in mysql explain bigger than real rows

Question

DB：Mysql 5.6, Innodb,

explain result:

the real data:

I'm confused where does the 16462900 come from. When I set 6 wave_no, the rows in explain result is 6:

Possible duplicate of [Why the rows returns by "explain" is not equal to count()?](http://stackoverflow.com/questions/1037471/why-the-rows-returns-by-explain-is-not-equal-to-count) — e4c5, Dec 15 '16 at 05:40

score 1 · Answer 1 · answered Dec 15 '16 at 05:43

1

The value of rows in the EXPLAIN output is an estimate of the number of rows that will be examined.

It's just an estimate, based on the calculated statistics.

References:

http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

http://dev.mysql.com/doc/refman/5.6/en/innodb-persistent-stats.html

answered Dec 15 '16 at 05:43

spencer7593

106,611
15
112
140

but the real row is 360408, is that possible that rows in explain much bigger than real row ? – Cedric Dec 15 '16 at 08:39
@Cedric: Yes, it's possible that the value reported for `rows` in the EXPLAIN output will be larger (or smaller) than the number of rows in the table. It's not an *exact* count of rows. The optimizer *estimates* the number of rows to be examined. (There's several reasons the estimates are a lot different... the statistics aren't up to date, distribution of key values on the pages that InnoDB used for statistics, et al.) The question you asked was "Where does the [rows value in EXPLAIN output] come from?." it comes from the optimizer, and is based on the table/index statistics. – spencer7593 Dec 15 '16 at 16:17
What do you mean "index doesn't work"? The EXPLAIN output is showing that the execution plan will use the `IDX_COM_WAVE` index. – spencer7593 Dec 15 '16 at 16:18

score 1 · Answer 2 · answered Dec 15 '16 at 18:47

Use this 'composite' index to improve performance:

INDEX(com_uid, exchange_state, wave_no)

And remove the FORCE.

The statistics are sometimes that far off. This can especially happen if there are TEXT or BLOB columns, which are stored elsewhere, thereby messing with the arithmetic. Don't worry about it.

You could do ANALYZE TABLE to recalculate the stats, but that might not improve the stats.

score 0 · Answer 3 · answered Apr 23 '21 at 08:37

My college said this because of table fragmentation, you can search it from Google, here is one .

MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient.

Here is the article in official site.

rows in mysql explain bigger than real rows

3 Answers3