A partitioned table in MySQL has its data separated into different tablespaces while still being viewed as a single table. Partitioning can be a useful approach in some cases when handling huge sets of data. Deleting huge data sets could be quickened up in a partitioned table, but if not handled properly, it can misplace your data in the table. In this blog, I will share how to check and fix the data in such a table with minimal disruption to the table.
In this example, we use a table partitioned based on a date range.
mysql> show create table salariesG *************************** 1. row *************************** Table: salaries Create Table: CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50500 PARTITION BY RANGE COLUMNS(from_date) (PARTITION p01 VALUES LESS …[Read more]