i am doing:
explain select * from calibration;
it says 52133456345632 rows
when i do:
select count(*) from calibration;
i am getting 52134563456961
can someone explain whats going on here?
views:
58answers:
1
+6
A:
Table statistics (used by EXPLAIN) are based on system-cached values that may not be accurate.
http://dev.mysql.com/doc/refman/5.1/en/using-explain.html says:
For InnoDB tables, this number is an estimate, and may not always be exact.
So the 'count()' version of the query will be accurate, as it will really 'count' existing rows. The 'explain' version does not necessarily count your rows, but might use an estimation/cache. Explain is not intended to be actually used in code or production - it is just a tool to help analysing your queries.
Bill Karwin
2010-06-02 21:33:26
how do i reset the cache?
I__
2010-06-02 21:34:35
I don't know, the manual page doesn't say. But the number is 99.8% accurate, do you really need it to be exact?
Bill Karwin
2010-06-02 21:36:06
@Bill Karwin: I added my comment to the question to your answer, as it was not intended to be an alternative but just a clarification. Hope that's OK.
Henrik Opel
2010-06-03 08:43:33
@Henrik Opel: No problem, thanks!
Bill Karwin
2010-06-03 17:05:19