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:

Answer 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

Answer 2

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.

Answer 3

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.