Adding partition to existing table in mysql gives wrong result

user_9548129 0
Viewed 310

I have created employee table in mysql with below details.

CREATE TABLE employee (
  id INT ,
  username varchar(50) ,
  store_id INT ,
   name VARCHAR(20)
);

-> insert into employee values(1,"ajay@gmail.com",79, "Ajay verma");
-> insert into employee values(2,"rahul@gmail.com",85, "rahul shaikh");
->insert into employee values(3,"vikas@gmail.com",302, "vikas malhotra");

Now I add range partition to above existing employee table having above records.

Alter table employee
PARTITION BY range columns(store_id)(
PARTITION p0 VALUES less than (100),
PARTITION p1 VALUES less than (200),
PARTITION p2 VALUES less than (300),
PARTITION p3 VALUES less than (400)
);

Now i am expecting since range partition is on store_id.So I will have 2 records in partition p0 for store_id 79 and 85 and 1 record in partition p3 for record having store_id 302.

But when I fire below query , I see different result

 SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_NAME, TABLE_ROWS,
 PARTITION_ORDINAL_POSITION,PARTITION_METHOD, PARTITION_EXPRESSION
 FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='employee';

o/p:-

enter image description here

Here in above output I can see only 2 records for partition p0 but there is not record for partition p3.But there should be total three records. 2 records for partition p0 and 1 record for partition p3. Why this above query on "INFORMATION_SCHEMA.PARTITIONS" gives wrong result?

partitioning mysql
1 Answers

As mysql documentation on information_schema.partitions table explains,

TABLE_ROWS

The number of table rows in the partition.

For partitioned InnoDB tables, the row count given in the TABLE_ROWS column is only an estimated value used in SQL optimization, and may not always be exact.

So, in your case the estimate is incorrect.

user_53899970
Related