Apparently UPDATE and DELETE don't use partition pruning

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)