Need help with partitioning in MySQL?

Question

MySQL

Good day!

I got a project for some database of logs received from very old servers.
The implementation was based on MS Access. That is, there was a set of databases, each database contained one table that contained log data from one of the servers. Each server has its own database. But the databases were also divided by years and half-years. The names are roughly formed as follows: server_year_half year (srv1_2020_1 or srv2_2020_2). And then there was a database and forms that allowed viewing these logs from these databases over the network.

Essence of the question.
I want to rewrite all this disgrace in the form of some web pages. And if I more or less imagine the appearance, and how the logic should work - too.
But here I rested on how the database should work.
The tables are all the same size. Something like id, datetime, text, etc.

There was an idea to reproduce tables according to the same principle as before, that is, a bunch of tables of the form server_year_half year. But I was prompted about partitioning.

The question is, I would like to organize partitioning in MySQL (if such an option is possible) of the following form: year -> month -> server

I was able to achieve only the option, either year -> month, or year -> server.

Code example (year month):

spoiler

CREATE TABLE aaa (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    server INT(1) UNSIGNED NOT NULL,
    logDate DATETIME NOT NULL,
    INDEX id (id)
)
PARTITION BY RANGE( YEAR(logDate) )
SUBPARTITION BY HASH( MONTH(logDate) )
SUBPARTITIONS 12 (
  PARTITION y20 VALUES LESS THAN (2020),
  PARTITION y21 VALUES LESS THAN (2021),
  PARTITION y22 VALUES LESS THAN (2022),
  PARTITION yNext VALUES LESS THAN MAXVALUE
);

Code example (server year):

spoiler

CREATE TABLE bbb (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    server INT(1) UNSIGNED NOT NULL,
    logDate DATETIME NOT NULL,
    INDEX id (id)
)
PARTITION BY RANGE (YEAR(logDate))
SUBPARTITION BY KEY(server)
SUBPARTITIONS 3 (
    PARTITION y2020 VALUES LESS THAN (2021) (
        SUBPARTITION srv0_2020,
        SUBPARTITION srv1_2020,
        SUBPARTITION srv2_2020
    ),
    PARTITION y2021 VALUES LESS THAN (2022) (
        SUBPARTITION srv0_2021,
        SUBPARTITION srv1_2021,
        SUBPARTITION srv2_2021
    ),
    PARTITION y2022 VALUES LESS THAN (2023) (
        SUBPARTITION srv0_2022,
        SUBPARTITION srv1_2022,
        SUBPARTITION srv2_2022
    )
);

Thanks for any help

Answer 1

In my opinion, there are a maximum of two levels (partitions and subpartitions).
You can, after all, make RANGE partitions by date at the month level (and sub-partitions by server):

PARTITION BY RANGE COLUMNS(datetime) (
    PARTITION 2019_01 VALUES LESS THAN ('2019-02-01'),
    PARTITION 2019_02 VALUES LESS THAN ('2019-03-01'),
    ...
);