Clever Geek Handbook

Creating a section in an existing mysql table

Below is my table structure, 150k records

CREATE TABLE `employees` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `emailid` varchar(255) DEFAULT NULL,
  `join_date` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`,`join_date`)
) ENGINE=InnoDB

I am using mysql version: 5.5.41

Data report:

+------------+-----------+
| join_date  | count     |
+------------+-----------+
| 2015-05-01 |       100 |
| 2015-05-02 |       100 |
| 2015-05-03 |       100 |
| 2015-05-04 |       100 |
| 2015-05-05 |       100 |
| 2015-05-06 |       100 |
| 2015-05-07 |     67900 |
| 2015-05-08 |     30622 |
| 2015-05-09 |     10455 |
| 2015-05-10 |     40393 |
+------------+-----------+

When I try to execute below command

Alter Table employees  PARTITION by RANGE (TO_DAYS(join_date)) 
      ( PARTITION p1 values less than (TO_DAYS('2015-05-07')));

I get the following error: The table does not have a section for the value 736090. Even though I have 600 records less than the value 2015-05-07

I am doing something wrong.

+3


source to share


1 answer


Not sure, but I think you should try:



ALTER TABLE employees PARTITION BY RANGE (TO_DAYS(join_date)) (
    PARTITION p1 values LESS THAN(TO_DAYS('2015-05-07')),
    PARTITION p2 VALUES LESS THAN(MAXVALUE)
);
+3


source






More articles:


All Articles