CREATE OR REPLACE TABLE t
|
(
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
timestamp DATETIME NOT NULL,
|
PRIMARY KEY (id, timestamp)
|
)
|
ENGINE = InnoDB
|
PARTITION BY RANGE (YEAR(timestamp))
|
(
|
PARTITION p0 VALUES LESS THAN (2010),
|
PARTITION p1 VALUES LESS THAN (2015),
|
PARTITION p2 VALUES LESS THAN (2020)
|
);
|
|
MariaDB [test]> EXPLAIN PARTITIONS UPDATE t SET timestamp = NOW() WHERE timestamp < 2000;
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+---------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+---------------------------+
|
| 1 | SIMPLE | t | p0,p1,p2 | index | NULL | PRIMARY | 12 | NULL | 3 | Using where; Using buffer |
|
+------+-------------+-------+------------+-------+---------------+---------+---------+------+------+---------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> EXPLAIN PARTITIONS DELETE FROM t WHERE timestamp < 2000;
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t | p0,p1,p2 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.01 sec)
|