Create a partial index on partitions or subpartitions of a partitioned table - PolarDB

A partial index covers only the partitions you specify, rather than the entire partitioned table. This keeps the index compact—useful when queries target specific partitions (such as recent months or a particular tenant group) while the rest of the data is accessed infrequently or not at all.

When to use a partial index

Use a partial index when your query workload concentrates on a subset of partitions:

Hot/cold data separation: Recent partitions receive heavy query traffic; older partitions are rarely queried. Index only the recent partitions to reduce index size and write overhead on cold data.
Tenant-specific queries: In multi-tenant tables, queries are scoped to specific tenant groups. Index only the relevant partitions to avoid maintaining a full-table index.
Selective range queries: Reports that always filter on a known partition range. A partial index on those partitions is smaller and faster to scan than a global index.

If queries regularly span all partitions, or the table is small enough that a full index is manageable, a global index may be more appropriate.

Create a partial index when creating a partitioned table

Add the partial_partition_option clause inside CREATE TABLE to define partial indexes at table creation time.

Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
    [(create_definition,...)]
    [table_options]
    partition_options
     ...

create_definition is:

{
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] [partial_partition_option]
      ...
}

partial_partition_option is:

([PARTITION partition_name0[(SUBPARTITION subpartition_name0[, ...])]][,...]
 )

For all other parameters, see CREATE TABLE.

Parameters

Parameter	Description
`table_name`	The table name.
`col_name`	The column name.
`partition_name0`	The partition name. Separate multiple partition names with commas (`,`).
`subpartition_name0`	The subpartition name. Separate multiple subpartition names with commas (`,`).

Examples

Index only the current month's partition

The following example creates an orders table partitioned by month (RANGE(month(date))) and defines two partial indexes:

o_ind_dp(dept_no, part_no) — covers only the December partition (orders_202212), which receives current-month writes and targeted queries.
o_ind_amout(amount, order_id) — covers the 11 historical partitions (orders_202201 through orders_202211), supporting amount-based reporting on past data.

CREATE TABLE orders
(
  order_id    INT,
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT,
  Primary Key(order_id),
  KEY o_ind_dp(dept_no, part_no) (partition orders_202212),
  KEY o_ind_amout(amount, order_id)
   (partition orders_202201,
    partition orders_202202,
    partition orders_202203,
    partition orders_202204,
    partition orders_202205,
    partition orders_202206,
    partition orders_202207,
    partition orders_202208,
    partition orders_202209,
    partition orders_202210,
    partition orders_202211
   )
)
PARTITION BY RANGE(month(date))
(
  PARTITION orders_202201 VALUES LESS THAN(2),
  PARTITION orders_202202 VALUES LESS THAN(3),
  PARTITION orders_202203 VALUES LESS THAN(4),
  PARTITION orders_202204 VALUES LESS THAN(5),
  PARTITION orders_202205 VALUES LESS THAN(6),
  PARTITION orders_202206 VALUES LESS THAN(7),
  PARTITION orders_202207 VALUES LESS THAN(8),
  PARTITION orders_202208 VALUES LESS THAN(9),
  PARTITION orders_202209 VALUES LESS THAN(10),
  PARTITION orders_202210 VALUES LESS THAN(11),
  PARTITION orders_202211 VALUES LESS THAN(12),
  PARTITION orders_202212 VALUES LESS THAN(13)
);

Index selected subpartitions in a multi-tenant table

The following example creates a LIST-RANGE subpartitioned tenants table and defines two partial indexes:

ind_id(id) — covers all subpartitions of p0 and subpartitions p1_1, p1_2, and p1_3 of p1.
ind_date(date) — covers subpartitions p0_1 and p0_2 of p0, and subpartitions p1_1 and p1_2 of p1.

CREATE TABLE tenants (
  id INT,
  date DATE,
  count INT,
  KEY ind_id (id)  (
    partition p0,
    partition p1 (subpartition p1_1, subpartition p1_2, subpartition p1_3)
  ),
  KEY ind_date (date) (
    partition p0 (subpartition p0_1,  subpartition p0_2),
    partition p1 (subpartition p1_1, subpartition p1_2)
  )
) PARTITION BY LIST COLUMNS(id)
  SUBPARTITION BY RANGE (month(date))(
  PARTITION p0 VALUES IN (1, 2, 3, 4, 5) (
    SUBPARTITION p0_1 VALUES LESS THAN(4),
    SUBPARTITION p0_2 VALUES LESS THAN(7),
    SUBPARTITION p0_3 VALUES LESS THAN(10),
    SUBPARTITION p0_4 VALUES LESS THAN(13)
  ),
  PARTITION p1 VALUES IN (11, 12, 13, 14, 15) (
    SUBPARTITION p1_1 VALUES LESS THAN(4),
    SUBPARTITION p1_2 VALUES LESS THAN(7),
    SUBPARTITION p1_3 VALUES LESS THAN(10),
    SUBPARTITION p1_4 VALUES LESS THAN(13)
  )
);

Create a partial index on an existing partitioned table

Use CREATE INDEX or ALTER TABLE ADD KEY to add a partial index to a table that already exists.

Syntax

CREATE [UNIQUE] INDEX index_name
    [index_type]
    ON table_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option | partial_partition_option] ...

partial_partition_option is:

([PARTITION partition_name0[(SUBPARTITION subpartition_name0[, ...])]][,...]
  )

For all other parameters, see CREATE INDEX.

Parameters

Parameter	Description
`index_name`	The index name.
`table_name`	The table name.
`partition_name0`	The partition name. Separate multiple partition names with commas (`,`).
`subpartition_name0`	The subpartition name. Separate multiple subpartition names with commas (`,`).

Examples

Add a partial index to a single partition

The following example adds a partial index o_part_id to the orders_202201 partition of the existing orders table:

CREATE INDEX o_part_id ON orders(part_no, order_id) (partition orders_202201);

Alternatively, use ALTER TABLE ADD KEY:

ALTER TABLE orders ADD KEY o_part_id(part_no, order_id) (partition orders_202201);

Add a partial index across partitions and subpartitions

The following example adds a partial index ind_count to all subpartitions of p0 and to subpartition p1_1 of p1 in the existing tenants table:

CREATE INDEX ind_count ON tenants(count) (partition p0, partition p1 (subpartition p1_1));

Alternatively, use ALTER TABLE ADD KEY:

ALTER TABLE tenants ADD KEY ind_count(count) (partition p0, partition p1 (subpartition p1_1));