CODEWITHSUNDEEP
mysqlperformanceexplain
Cedric
Cedric

Reputation: 95

rows in mysql explain bigger than real rows

DB：Mysql 5.6, Innodb,the index

explain result: explain result

the real data: enter image description here

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

Upvotes: 1

Views: 1310

Answers (3)

Jess
Jess

Reputation: 3715

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.

Upvotes: 0

Rick James
Rick James

Reputation: 142298

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.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108400

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

Upvotes: 1

Related Questions