PolarDB:MMDD

How it works

MMDD derives the table shard subscript from the month and day portion of the partitioning key value. A row with a create_time of 2024-03-15 maps to table shard 0315, placing it in the shard that represents March 15.

Because MMDD routes by day of year, each database shard holds at most 366 table shards — one per possible calendar day.

Limitations

Use cases

Use MMDD when you need to partition a large table by day of year and query patterns align with specific calendar days. The table shard subscript directly represents a calendar day, making day-based range queries efficient.

MMDD is not suitable for:

• Database sharding — MMDD only works for table shards.

• Even data distribution regardless of date — if your data volume varies significantly by day (for example, heavy traffic on weekdays and low traffic on weekends), MMDD will produce unevenly sized shards. Use a hash-based function for uniform distribution.

Create a table with MMDD sharding

The following Data Definition Language (DDL) example partitions data into database shards by user name (hash) and into table shards by calendar day using MMDD on the create_time column.

CREATE TABLE test_mmdd_tb (
    id          INT,
    name        VARCHAR(30)  DEFAULT NULL,
    create_time DATETIME     DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
  dbpartition  BY HASH(name)
  tbpartition  BY MMDD(create_time) tbpartitions 366;
  -- 366 tbpartitions: one per possible calendar day (a year has at most 366 days)

Key parameters: