Create partition on MySQL based on a date column:

CREATE TABLE members (
     firstname VARCHAR(25) NOT NULL,
     lastname VARCHAR(25) NOT NULL,
     username VARCHAR(16) NOT NULL,
     email VARCHAR(35),
     joined DATE NOT NULL
 )
 PARTITION BY RANGE COLUMNS(joined) (
     PARTITION p0 VALUES LESS THAN ('1960-01-01'),
     PARTITION p1 VALUES LESS THAN ('1970-01-01'),
     PARTITION p2 VALUES LESS THAN ('1980-01-01'),
     PARTITION p3 VALUES LESS THAN ('1990-01-01'),
     PARTITION p4 VALUES LESS THAN MAXVALUE
 );

MySQL subdivide table into smaller tables based on date in low level. It improves performance in case you want to query based on date
field and another column. There is also other partitioning types like LIST, HASH, KEY.

Delete a specific partition in MySQL:

ALTER TABLE members DROP PARTITION p0;

The above command is much more efficient than command below:

DELETE FROM members WHERE joined <= '1990-01-01';

If your field is of type timestamp you can do something like below:

CREATE TABLE quarterly_report_status (
     report_id INT NOT NULL,
     report_status VARCHAR(20) NOT NULL,
     report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 )
 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
     PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
     PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
     PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
     PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
     PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
     PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
     PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
     PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
     PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
     PARTITION p9 VALUES LESS THAN (MAXVALUE)
 );

#mysql #partition #range_partition #database