I've created a table with range partition as
CREATE TABLE nums_range (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id)
) PARTITION BY RANGE(id) (
PARTITION p0mil VALUES LESS THAN (1000000),
PARTITION p1mil VALUES LESS THAN (2000000),
PARTITION p2mil VALUES LESS THAN (3000000),
PARTITION p3mil VALUES LESS THAN (4000000),
PARTITION p4mil VALUES LESS THAN (5000000)
);
And I've Inserted following values
INSERT INTO nums_range (id) VALUES
(900000),(1),(1500050),(1699876),(2700000),(3000000),(3500000),(4000000),(4500000);
So when I type the following query
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'nums_range';
I get a table showing how partitions has been done...and that's nice. To view the table Click Here. You might have observed from the figure that Partition p2mil has one row of data and others are having 2 rows each. When I drop the first partition i.e. p0mil, then the data belongs to that partition gets deleted. But the problem is when I select remaining partitions, Partitions p2mil shows 0 row though It actually has its row. Why??? Click Here to see commands with output for better understanding my problem.
Please Help me Since I'm new to MySQL Partitioning. Thank You :)
This happens because MySQL does not automatically update the stats of the existing partitions. You need to analyze the remaining partitions again, alter table nums_range analyze PARTITION p1mil; alter table nums_range analyze PARTITION p2mil; alter table nums_range analyze PARTITION p3mil;
After running the above commands, rerun your query to check, you should only see 7 records in the table
