MySQL Forums
Forum List  »  Partitioning

Re: Mysql 5.5 Partittioning row field
Posted by: Rick James
Date: June 26, 2015 12:15AM

Jon, I beg to differ.

The reference says " The rows column indicates the number of rows MySQL believes it must examine to execute the query. For InnoDB tables, this number is an estimate, and may not always be exact."

To parse the documentation backward:

1. InnoDB makes an estimate of how many rows to look at. This estimate can be (in my experience) as much as a factor of two high _or_ low. Shubham sees only 1% difference; this is unusually precise.

2. That number does not necessarily agree with reality.

That is, EXPLAIN rarely knows the exact "number of rows that MySQL needs to check" (quoting you).

Furthermore, it would be counter-productive to discover the exact number of rows -- In many cases, that would imply that EXPLAIN would have to perform the query! Shubham, am I correct in saying that the EXPLAIN was _much_ faster than the SELECT?

There was an "improvement" in 5.6(?) relating to subqueries. Before then, EXPLAIN _would_ evaluate the subquery, possibly taking a lot of time. After then, EXPLAIN would only estimate.

-- Rick James

Options: ReplyQuote

Written By
June 24, 2015 10:19PM
Re: Mysql 5.5 Partittioning row field
June 26, 2015 12:15AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.