Bug #40912 partition rebuild causes differeces in rowcount
Submitted: 21 Nov 2008 3:15 Modified: 21 Dec 2008 6:36
Reporter: kris dba Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1 OS:Linux
Assigned to: CPU Architecture:Any
Tags: Optimize, partition, rebuild

[21 Nov 2008 3:15] kris dba
We have a huge partitioned table activity_details with Innodb storage.
The table was partitioned by range.
Please confirm if there would be any issue in rebuilding the partitions to get the performance boost.

How to repeat:
rebuild the partitions of a table.
[21 Nov 2008 4:36] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.1.29, and inform about the results.
[21 Nov 2008 5:40] kris dba
thank you
[21 Nov 2008 6:36] Sveta Smirnova
Thank you for the feedback.

According to http://dev.mysql.com/doc/refman/5.1/en/partitioning-maintenance.html: "Beginning with MySQL 5.1.5, you can also rebuild partitions using ALTER TABLE ... REBUILD PARTITION, which has the same effect as dropping all records stored in the partition, then reinserting them." Which means this statement should not change quantity of rows. So, please, upgrade to current version 5.1.29 and inform us if problem still exists.
[15 Dec 2008 8:02] Mattias Jonsson
Hi, the row count in 'SHOW TABLE STATUS' is not exact for InnoDB, so to correctly check the number of rows, please use 'SELECT COUNT(*)'

From http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html


The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.
[22 Dec 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".