I need to partition table date wise and then this partition needs to be sub partition by userid. I have partition table like below:
ALTER TABLE sentbox1 PARTITION BY RANGE(to_days(CreatedDateTime))(
PARTITION p20161130 VALUES LESS THAN (to_days('2016-11-30')),
PARTITION p20161201 VALUES LESS THAN (to_days('2016-12-01'))
);
Now,How to add sub partition by userid into partitions 'p20161130' and 'p20161201'.
I tested this on MySQL 8.0.0-dmr:
ALTER TABLE sentbox1
PARTITION BY RANGE(to_days(CreatedDateTime))
SUBPARTITION BY HASH(userid)
SUBPARTITIONS 4 (
PARTITION p20161130 VALUES LESS THAN (to_days('2016-11-30')),
PARTITION p20161201 VALUES LESS THAN (to_days('2016-12-01'))
);
Note that subpartitioning works only with HASH or KEY as the partitioning method.
See https://dev.mysql.com/doc/refman/5.7/en/partitioning-subpartitions.html for more information.
Re comment from Rick:
@RickJames, I think you know that depends entirely on whether the query one run aligns with the partitioning and subpartitioning. If your query logic causes the query to span subpartitions, it gives no benefit.
I did confirm that partition pruning heeds conditions on the subpartition key.
When I put a condition only on the major partition key, it has to scan all subpartitions of that partition:
mysql> explain partitions select * from sentbox1
where createddatetime = from_days(736663) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sentbox1
partitions: p20161201_p20161201sp0,
p20161201_p20161201sp1,
p20161201_p20161201sp2,
p20161201_p20161201sp3
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
When I additionally specify a condition on the userid (the subpartition key), we see a decreased subset of subpartitions to be examined:
mysql> explain partitions select * from sentbox1
where createddatetime = from_days(736663) and userid=3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sentbox1
partitions: p20161201_p20161201sp3
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
HASH or SUBPARTITION? (I present this as a challenge to you, Bill.)Sometimes the answer to a question is "don't". I have yet to find any use for SUBPARTITION. Please explain the use case where you think this will help.
In particular, partitioning does not intrinsically provide any performance benefit.
The usual use case for BY RANGE(TO_DAYS(..)) is when you need to delete 'old' data. Doing DROP PARTITION is significantly more efficient than DELETE. Is that the case you have? If so, having SUBPARTITIONs does not help in any way that I know of.
Meanwhile, I have found no use case for HASH. Sure, it may do pruning on userid, but that pruning is no better than having userid at the start of a composite index. And it is likely to be worse.
