MySQl partitioning query issue

[Madhu Venkatesh]

Hello All,

I have created partitions p0,p1,p2,p3,p4,p5,p6..p11

P11 is the partition that is created by date range less than Dec 2011.. So all the data inserted in NOV month should go to P11.

When I execute the below query to know how many rows are inserted into a NOV partition P11, it keeps showing 2 differnt values as shown below:



mysql> SELECT partition_name, index_length, data_length, table_rows FROM information_schema.partitions where table_name='CCSF_AGENT_ACTIVITY_HISTORY';
+----------------+--------------+-------------+------------+
| partition_name | index_length | data_length | table_rows |
+----------------+--------------+-------------+------------+
| p0 | 16384 | 16384 | 0 |
| p11 | 16384 | 49152 | 489 |
| p12 | 16384 | 16384 | 0 |
| p1 | 16384 | 16384 | 0 |
| p2 | 16384 | 16384 | 0 |
| p3 | 16384 | 16384 | 0 |
| p4 | 16384 | 16384 | 0 |
| p5 | 16384 | 16384 | 0 |
| p6 | 16384 | 16384 | 0 |
+----------------+--------------+-------------+------------+
9 rows in set (0.00 sec)


mysql> SELECT partition_name, index_length, data_length, table_rows FROM information_schema.partitions where table_name='CCSF_AGENT_ACTIVITY_HISTORY';
+----------------+--------------+-------------+------------+
| partition_name | index_length | data_length | table_rows |
+----------------+--------------+-------------+------------+
| p0 | 16384 | 16384 | 0 |
| p11 | 16384 | 49152 | 321 |
| p12 | 16384 | 16384 | 0 |
| p1 | 16384 | 16384 | 0 |
| p2 | 16384 | 16384 | 0 |
| p3 | 16384 | 16384 | 0 |
| p4 | 16384 | 16384 | 0 |
| p5 | 16384 | 16384 | 0 |
| p6 | 16384 | 16384 | 0 |
+----------------+--------------+-------------+------------+
9 rows in set (0.00 sec)


But when I did count(*) of that table for the NOV month, the count is 404.

Could anyone explain this weird behavior??