Skip navigation links

MySQL Forums :: Partitioning :: MySQl partitioning query issue


Advanced Search

MySQl partitioning query issue
Posted by: Madhu Venkatesh ()
Date: November 02, 2011 10:34AM

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??

Options: ReplyQuote


Subject Views Written By Posted
MySQl partitioning query issue 2086 Madhu Venkatesh 11/02/2011 10:34AM
Re: MySQl partitioning query issue 817 Madhu Venkatesh 11/02/2011 12:33PM
Re: MySQl partitioning query issue 871 Rick James 11/05/2011 08:42PM


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.