MySQL Forums :: Partitioning :: Mysql 5.5 Partittioning row field

Advanced Search

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

Subject Views Written By Posted
Mysql 5.5 Partittioning row field 1354 Shubham Jain 06/24/2015 10:19PM
Re: Mysql 5.5 Partittioning row field 978 Jonathan Stephens 06/25/2015 10:16AM
Re: Mysql 5.5 Partittioning row field 801 Rick James 06/26/2015 12:15AM
Re: Mysql 5.5 Partittioning row field 957 Jonathan Stephens 06/26/2015 02:40AM
Re: Mysql 5.5 Partittioning row field 831 Shubham Jain 06/26/2015 07:02AM

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.