0

I read the docs about subpartitioning, but I think it doesn't cover what I'm trying to achieve. https://dev.mysql.com/doc/refman/5.7/en/partitioning-subpartitions.html

We have this table:

CREATE TABLE `posts_countries` (
  `customer_id` int unsigned NOT NULL,
  `partition_date` mediumint unsigned DEFAULT '0',
  `post_id` int unsigned NOT NULL,
  `country` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `impressions` int NOT NULL,
  `engagement` int DEFAULT NULL,
  PRIMARY KEY (`customer_id`,`post_id`,`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

We have MILLON rows, performance is slow in every single query so we want to create PARTITION with MySQL. Some of the tests improved performance, because we always query by customer_id and by post_id.

What I want to achieve now is to make a PARTITION by customer_id, and then by partition_date. Partition date for example, stores 202207 as July 2022, and so on.

We want to create only 13 partitions by customer (he have very few customers). So, partition 1 would be:

Customer 1 -> VALUES LESS THAN MAX
Customer 1 -> VALUES LESS THAN 202207
Customer 1 -> VALUES LESS THAN 202206
...
Customer 1 -> VALUES LESS THAN 202107
...
...

Customer 2 -> VALUES LESS THAN MAX
Customer 2 -> VALUES LESS THAN 202207
Customer 2 -> VALUES LESS THAN 202206
...
Customer 2 -> VALUES LESS THAN 202107

I tried partitioning this way, but it's not partitioning by client_id

ALTER TABLE posts_countries
    PARTITION BY RANGE (partition_date)
SUBPARTITION BY HASH(customer_id)
SUBPARTITIONS 10
(
PARTITION 0c1 VALUES LESS THAN (202108) ENGINE = InnoDB,
 PARTITION 0c2 VALUES LESS THAN (202109) ENGINE = InnoDB,
 PARTITION 0c3 VALUES LESS THAN (202110) ENGINE = InnoDB,
 PARTITION 0c4 VALUES LESS THAN (202111) ENGINE = InnoDB,
 PARTITION 0c5 VALUES LESS THAN (202112) ENGINE = InnoDB,
 PARTITION 0c6 VALUES LESS THAN (202201) ENGINE = InnoDB,
 PARTITION 0c7 VALUES LESS THAN (202202) ENGINE = InnoDB,
 PARTITION 0c8 VALUES LESS THAN (202203) ENGINE = InnoDB,
 PARTITION 0c9 VALUES LESS THAN (202204) ENGINE = InnoDB,
 PARTITION 0c10 VALUES LESS THAN (202205) ENGINE = InnoDB,
 PARTITION 0c11 VALUES LESS THAN (202206) ENGINE = InnoDB,
 PARTITION 0c12 VALUES LESS THAN (202207) ENGINE = InnoDB,
 PARTITION 0c13 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB
);

Is this partitioning structure possible? Thanks in advice.

2 Answers 2

0

Nothing prevents.

CREATE TABLE test (id INT, grp INT, val INT)
    PARTITION BY RANGE( grp )
    SUBPARTITION BY HASH( val )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (10),
        PARTITION p1 VALUES LESS THAN MAXVALUE
    );
INSERT INTO test VALUES
(1,1,1),
(2,10,2),
(3,20,3),
(4,50,4),
(5,80,5),
(6,2000,6);
SELECT TABLE_NAME,
       PARTITION_NAME,
       SUBPARTITION_NAME,
       TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE();
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLE_ROWS
test p0 p0sp0 0
test p0 p0sp1 1
test p1 p1sp0 3
test p1 p1sp1 2

db<>fiddle here

SUBPARTITION BY KEY( val ) is available too.

0

Partitioning is not a performance panacea. Don't go through the effort of adding partitions until you check (ask me) whether it will speed up your queries.

The main cases where you might need partitioning:

  • You will be purging old data and want to use DROP PARTITION instead of DELETE.
  • The queries are "2-dimensional" with ranges in both -- as with latitude and longitude.

Show us your main queries. It smells like they look something like

SELECT ...
    FROM ...
    WHERE customer_id = 4
      AND some_date BETWEEN ...

Well, this 'composite' index works perfectly well for a non-partitioned table:

INDEX(customer_id, some_date)

(That's not really 2-dimensional, since you will always(?) use = when testing customer_id.)

More discussion: Partition

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.