mysql partitioning. How to split a table by list and date?

Question

MySQL

Hello. There is the following table (simplified):

CREATE TABLE `routes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`created` datetime NOT NULL,
`createdTimezoneOffset` smallint(6) NOT NULL DEFAULT '0',
`deleted` tinyint(1) DEFAULT '0',
 PRIMARY KEY (`id`,`created`,`archive`)
) ENGINE=InnoDB AUTO_INCREMENT=1014699 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

It is necessary to divide the table as follows:

Section: Archive = 0
Subsection: Year of creation

Section: Archive = 1
Subsection: Year of creation

I do this:

ALTER TABLE routes PARTITION BY LIST (archive)
SUBPARTITION BY HASH (YEAR(created))
(
   PARTITION p0 VALUES IN (0)
   (
      SUBPARTITION s0,
      SUBPARTITION s1,
      SUBPARTITION s2,
      SUBPARTITION s3
   ),
   PARTITION p1 VALUES IN (1)
   (
      SUBPARTITION s4,
      SUBPARTITION s5,
      SUBPARTITION s6,
      SUBPARTITION s7
   )
);

And everything seems to be working. But when I make a selection by the period of the created field, all sections are read.

Пример:  EXPLAIN SELECT * FROM routes WHERE archive = 1 AND created >= '2020-01-01 00:00:00';

Based on the documentation - I understand why.
What is the correct way to split the table in such a way that the query above takes a specific part of the table?

Answer 1

Solution found. Works as it should, I'll leave it here:

ALTER TABLE routes PARTITION BY RANGE COLUMNS (archive, created)
(
    PARTITION p0 VALUES LESS THAN (0, MAXVALUE),
    PARTITION p1 VALUES LESS THAN (1, '2019-01-01'),
    PARTITION p2 VALUES LESS THAN (1, '2020-01-01'),
    PARTITION p3 VALUES LESS THAN (1, '2020-06-01'),
    PARTITION p4 VALUES LESS THAN (1, MAXVALUE)
);