The MM function partitions table data by month of the year. Each table shard maps to one calendar month, so dropping or archiving a month's data requires a single shard-level operation instead of a full-table scan and delete.
How it works
MM extracts the month value (1–12) from the shard key's time value and uses that value as the table shard subscript. Rows with a create_time in January route to shard 1, rows in February route to shard 2, and so on.
When to use MM
Use MM when your query patterns align with calendar months:
Monthly billing cycles — generate or archive monthly invoices by operating on the corresponding shard
Monthly reports — query a single shard instead of scanning the full table
Monthly data archival or purging — drop an entire month's shard with a single DDL operation, which is faster than a row-level
DELETE
Avoid MM when your access patterns span multiple months or are not time-based. In those cases, a hash-based or range-based sharding function is a better fit.
Limitations
| Constraint | Detail |
|---|---|
| Supported partitioning key types | DATE, DATETIME, TIMESTAMP |
| Scope | Table sharding (tbpartition) only — not database sharding (dbpartition) |
| Maximum table shards per database shard | 12 (one per month) |
| Minimum PolarDB-X 1.0 version | 5.1.28-1320920 |
Example
The following Data Definition Language (DDL) statement creates a table sharded by user ID at the database level and by month at the table level. With tbpartitions 12, PolarDB-X 1.0 creates one table shard per calendar month.
CREATE TABLE test_mm_tb (
id INT,
name VARCHAR(30) DEFAULT NULL,
create_time DATETIME DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
dbpartition BY HASH(id)
tbpartition BY MM(create_time) tbpartitions 12;