All Products
Search

This Product

    PolarDB:CREATE INDEX (DRDS mode)

    Document Center

    PolarDB:CREATE INDEX (DRDS mode)

    Last Updated:Mar 30, 2026

    PolarDB-X supports two types of secondary indexes for DRDS mode databases: local secondary indexes and global secondary indexes (GSI). This page covers the syntax for creating both types and how to verify the results.

    This syntax applies only to DRDS mode databases.

    Local secondary index

    Local secondary indexes follow standard MySQL CREATE INDEX syntax. See CREATE INDEX Statement in the MySQL documentation.

    Global secondary index

    A global secondary index (GSI) distributes its index data across shards independently of the base table. Unlike a local index, which is physically collocated with the base table data in each shard, a GSI maintains its own index table — sharded by the GSI shard key — allowing cross-shard queries to be resolved without a full-table scan.

    For an introduction to GSI concepts, see GSI.

    When to use a GSI

    Situation Use
    Queries frequently filter or join on a column that is not the base table's shard key GSI
    Queries span multiple database shards and need to avoid full-shard scans GSI
    Queries are always limited to a single shard Local secondary index
    Data is archived or purged frequently at the shard level Local secondary index

    Syntax

    CREATE [UNIQUE]
    GLOBAL INDEX index_name [index_type]
    ON tbl_name (index_sharding_col_name,...)
    global_secondary_index_option
    [index_option]
    [algorithm_option | lock_option] ...

global_secondary_index_option:
    [COVERING (col_name,...)]
    drds_partition_options
    [VISIBLE|INVISIBLE]

drds_partition_options:
    DBPARTITION BY db_sharding_algorithm
    [TBPARTITION BY {table_sharding_algorithm} [TBPARTITIONS num]]

db_sharding_algorithm:
    HASH([col_name])
  | {YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n)

table_sharding_algorithm:
    HASH(col_name)
  | {MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n)

index_sharding_col_name:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

    Parameters

    Parameter Description
    UNIQUE Creates a unique GSI. Enforces uniqueness across all shards of the index table.
    index_name The name of the GSI. The underlying index table is created with this name.
    index_type The index structure. Use USING BTREE or USING HASH. Defaults to B-tree.
    tbl_name The base table on which to create the GSI.
    index_sharding_col_name The shard key column(s) of the GSI. The length prefix can only be used when creating a local secondary index based on the index table's shard key; it is not applicable to the GSI shard key column itself.
    COVERING (col_name,...) Additional columns to store in the index table beyond the default covering columns. Storing extra columns in the index table lets queries retrieve those values without joining back to the base table.
    DBPARTITION BY Required. The database-level sharding algorithm for the index table.
    TBPARTITION BY Optional. The table-level sharding algorithm within each database shard.
    TBPARTITIONS num Optional. The number of table shards per database shard. Used together with TBPARTITION BY.
    VISIBLE | INVISIBLE Controls whether the optimizer considers this index. An invisible index is maintained but ignored by the query optimizer, which is useful for testing index removal without dropping it.
    ALGORITHM DDL execution mode: DEFAULT (engine chooses), INPLACE (no table rebuild), or COPY (table rebuild).
    LOCK Concurrency control during DDL: DEFAULT, NONE (no lock), SHARED (read lock), or EXCLUSIVE (read and write lock).

    Default covering columns

    Every GSI index table automatically includes the base table's primary key and shard key as covering columns, even if you do not specify COVERING. Custom covering columns added via COVERING are stored in addition to these defaults.

    Sharding algorithms

    db_sharding_algorithm and table_sharding_algorithm share most of the same options. The key difference is that table_sharding_algorithm additionally supports time-based sub-month granularity: MM, DD, WEEK, and MMDD.

    Before creating a GSI on an existing table, review the limits and conventions. See How to use global secondary indexes.

    For details on partition clauses used in CREATE TABLE, see CREATE TABLE (DRDS mode).

    Example

    The following example creates an order table sharded by order_id, then adds a GSI on buyer_id with order_snapshot as a custom covering column.

    -- Create the base table, sharded by order_id
CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);

-- Add a unique GSI on buyer_id, with order_snapshot as a custom covering column
ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_snapshot`) dbpartition by hash(`buyer_id`);

    After this statement runs:

    • Base table (t_order): One table per database shard, sharded by hash on order_id.

    • Index table (g_i_buyer): One index table per database shard, sharded by hash on buyer_id. The index table contains buyer_id (shard key), id (base table primary key), order_id (base table shard key — default covering columns), and order_snapshot (custom covering column). The primary key in the index table is not auto-increment.

    Verify indexes

    Use the following statements to inspect the indexes on t_order after creation.

    SHOW INDEX

    SHOW INDEX lists all indexes on the table, including both local and global secondary indexes. GSI columns appear with INDEX_TYPE = GLOBAL. The COMMENT column indicates whether a column is part of the index key (INDEX) or a covering column (COVERING).

    show index from t_order;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| TABLE   | NON_UNIQUE | KEY_NAME   | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULL | INDEX_TYPE | COMMENT  | INDEX_COMMENT |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| t_order |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |          |               |
| t_order |          1 | l_i_order  |            1 | order_id    | A         |           0 |     NULL | NULL   | YES  | BTREE      |          |               |
| t_order |          1 | g_i_seller |            1 | seller_id   | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | INDEX    |               |
| t_order |          1 | g_i_seller |            2 | id          | NULL      |           0 |     NULL | NULL   |      | GLOBAL     | COVERING |               |
| t_order |          1 | g_i_seller |            3 | order_id    | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

    For more information, see SHOW INDEX.

    SHOW GLOBAL INDEX

    SHOW GLOBAL INDEX returns GSI-specific metadata, including the partition policy and shard counts for both the database and table levels.

    show global index from t_order;
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| SCHEMA              | TABLE   | NON_UNIQUE | KEY_NAME   | INDEX_NAMES | COVERING_NAMES | INDEX_TYPE | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT | STATUS |
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| ZZY3_DRDS_LOCAL_APP | t_order | 1          | g_i_seller | seller_id   | id, order_id   | NULL       | seller_id        | HASH                | 4                  |                  | NULL                | NULL               | PUBLIC |
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

    For more information, see SHOW GLOBAL INDEX.

    SHOW CREATE TABLE

    SHOW CREATE TABLE on the index table name reveals the exact schema of the GSI index table, confirming which columns are stored and how the index table is partitioned.

    show create table g_i_seller;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                  |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| g_i_seller | CREATE TABLE `g_i_seller` (
  `id` bigint(11) NOT NULL,
  `order_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `auto_shard_key_seller_id` (`seller_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`seller_id`) |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

    Notice that the primary key (id) is not auto-increment in the index table, and the index table contains no local secondary indexes.

    What's next