Range Partitioning in Mysql

Each partition contains rows for which the partitioning expression value lies within a given range.


create table systemlog
(id int ,datadate datetime not null,val  varchar(10) not null,
primary key (val,datadate)
)
partition by range(year(datadate))
(
 partition fp0 values less than (2014),
 partition fp1 values less than (2015),
 partition fp2 values less than (2016),
 partition fp3 values less than maxvalue
);


RANGE partitioning works best for:

insert into systemlog values
(1,'2013-01-01','A'),(2,'2014-01-01','A'),(3,'2015-01-01','A'),(4,'2016-01-01','A')
  

SELECT PARTITION_NAME, TABLE_ROWS FROM
INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='systemlog';
 

Output :

 # PARTITION_NAME, TABLE_ROWS
    'fp0'                        ,     '3'
    'fp1'                        ,     '2'
    'fp2'                        ,     '1'
    'fp3'                        ,     '2'

show create table systemlog ;
 

CREATE TABLE `systemlog` (
  `id` int(11) DEFAULT NULL,
  `datadate` datetime NOT NULL,
  `val` varchar(10) NOT NULL,
  PRIMARY KEY (`val`,`datadate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`datadate`))
(PARTITION fp0 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION fp1 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION fp2 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION fp3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

explain
select * from systemlog  partition(fp0)  where year(datadate)='2014'

Deleting Old Data: In the above example, if logs from 2013 need to be deleted, you can simply use ALTER TABLE userslogs DROP PARTITION from_2013_or_less;

ALTER TABLE systemlog TRUNCATE PARTITION fp0;