Why the rows returned in EXPLAIN and SHOW TABLE STATUS LIKE is not accurate
This article introduces why the rows returned in EXPLAIN and SHOW TABLE STATUS LIKE is not accurate. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.
"Why isn't the rows returned in SHOW TABLE STATUS LIKE accurate?" Today, I will take a moment to talk about it briefly!
First of all, let's take a literal view of the number of lines represented by rows. The exact number of rows affected, or the number of rows scanned!
In theory, my actual query on the number of rows scanned should be accurate. But the display here is not accurate, MySQL statistics are not accurate, is MySQL deliberately designed like this? If so, then why is it designed this way?
Obviously, MySQL knows about the inaccuracy. Then it must be designed for a reason.
As we all know, MySQL has an optimizer that makes some optimizations to your SQL. The principle of the optimizer is to estimate the cost of some of each execution strategy before actually implementing the SQL and choose what it thinks is the best solution to implement!
Because the optimizer makes a judgment before it is actually executed, it cannot be accurate. The optimizer can only count and estimate the number of rows to be scanned according to the "differentiation" of the index.
Here, MySQL is in order to manipulate the data more efficiently. Using the knowledge of mathematics, a sampling statistics is made.
Sampling, that is, InnoDB will select N data pages by default, count the different values on these pages, get an average, and then multiply by the number of pages of the index to get the cardinality of the index.
The above explains why the rows returned in EXPLAIN and SHOW TABLE STATUS LIKE is not accurate!
About why the rows returned in EXPLAIN and SHOW TABLE STATUS LIKE is not accurate to share here, I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.
