mariadb (mysql) sub partition error (total sub partition count exceeds 64)

Question

enter image description here

Hello

I want to configure a partition (monthly)/subpartition (day by day) as the query above. If the total number of subpartitions exceeds 64, '(errno: 168 "Unknown (generic) error from engine")' The table is not created due to an error. (Creating less than 64 is successed).

I know that the maximum number of partitions (including subpartitions) that can be created is 8,192, is there anything I missed?

Below is the log table.

create table detection_log
(
    id bigint auto_increment,
    detected_time datetime default  '1970-01-01' not null,
    malware_title varchar(255) null,
    malware_category varchar(30) null,
    user_name varchar(30) null,
    department_path varchar(255) null,
    PRIMARY KEY (detected_time, id),
    INDEX `detection_log_id_uindex` (id),
    INDEX `detection_log_malware_title_index` (malware_title),
    INDEX `detection_log_malware_category_index` (malware_category),
    INDEX `detection_log_user_name_index` (user_name),
    INDEX `detection_log_department_path_index` (departmen`enter code here`t_path)
);

Answer 1

• SUBPARTITIONs provide no benefit that I know of.
• HASH partitioning either provides no benefit or hurts performance.

So... Explain what you hoped to gain by partitioning; then we can discuss whether any type of partitioning is worth doing. Also, provide the likely SELECTs so we can discuss the optimal INDEXes. If you need a "two-dimensional" index, that might indicate a need for partitioning (but still not subpartitioning).

More

I see PRIMARY KEY(detected_time,id). This provides a very fast way to do

SELECT ...
    WHERE detected_time BETWEEN ... AND ...
    ORDER BY  detected_time, id

In fact, it will probably be faster than if you also partition the table. (As a general rule it is useless to partition on the first part of the PK.)

If you need to do

SELECT ...
    WHERE user_id = 123
      AND detected_time BETWEEN ... AND ...
    ORDER BY  detected_time, id

Then this is optimal:

INDEX(user_id, detected_time, id)

Again, probably faster than any form of partitioning on any column(s).

And

A "point query" (WHERE key = 123) takes a few milliseconds more in a 1-billion-row table compared to a 1000-row table. Rarely is the difference important. The depth of the BTree (perhaps 5 levels vs 2 levels) is the main difference. If you PARTITION the table, you are removing perhaps 1 or 2 levels of the BTree, but replacing them with code to "prune" down to the desired partition. I claim that this tradeoff does not provide a performance benefit.

A "range query" is very nearly the same speed regardless of the table size. This is because the structure is actually a B+Tree, so it is very efficient to fetch the 'next' row.

Hence, the main goal in optimizing queries on a huge table is to take advantage of the characteristics of the B+Tree.

Pagination

SELECT  log.detected_time, log.user_name, log.department_path,
        log.malware_category, log.malware_title
    FROM  detection_log as log
    JOIN  
    (
        SELECT  id
            FROM  detection_log
            WHERE  user_name = 'param'
            ORDER BY  detected_time DESC
            LIMIT  25 OFFSET 1000 
    ) as temp  ON temp.id = log.id;

The good part: Finding ids, then fetching the data.

The slow part: Using OFFSET.

Have this composite index: INDEX(user_name, detected_time, id) in that order. Make another index for when you use department_path.

Instead of OFFSET, "remember where you left off". A blog specifically about that: http://mysql.rjweb.org/doc.php/pagination

Purging

Deleting after a year is an excellent use of PARTITIONing. Use PARTITION BY RANGE(TO_DAYS(detected_time)) and have either ~55 weekly or 15 monthly partitions. See HTTP://mysql.rjweb.org/doc.php/partitionmaint for details. DROP PARTITION is immensely faster than DELETE. (This partitioning will not speed up SELECT.)