Create Global and Local Secondary Indexes with CREATE INDEX - PolarDB

Use CREATE INDEX to add a local secondary index (LSI) or a global secondary index (GSI) to an existing table in PolarDB-X 1.0.

LSI

LSI syntax follows the standard MySQL CREATE INDEX statement. For the full syntax reference, see MySQL CREATE INDEX.

GSI

A GSI spans all database shards and lets queries filter on non-shard-key columns without scanning every shard. Use a GSI when queries must filter on columns other than the primary table's shard key.

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] ...

-- GSI-specific options
global_secondary_index_option:
    [COVERING (col_name, ...)]
    drds_partition_options

-- Sharding options for the index table
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)

-- The length parameter applies only when creating LSIs on shard keys of an index table
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`	Enforces a global unique constraint across all shards. PolarDB-X automatically creates a local unique index on the index table covering all GSI index columns to maintain this constraint.
`GLOBAL`	Required keyword that designates the index as a GSI. Without `GLOBAL`, `CREATE INDEX` creates a standard MySQL local index.
`index_name`	Name of the index. Must be unique within the table.
`index_sharding_col_name`	The shard key column(s) for the index table. Determines how data in the index table is distributed across shards.
`COVERING (col_name, ...)`	Additional columns to store in the index table. When a query filters on the GSI shard key and selects only covered columns, PolarDB-X reads directly from the index table without joining back to the primary table. Without this clause, only the primary key and primary table shard key are stored as default covering columns, and queries that select other columns require a join back to the primary table.
`DBPARTITION BY`	Required. Specifies the database-level sharding algorithm for the index table.
`TBPARTITION BY`	Optional. Specifies the table-level sharding algorithm and the number of table shards (`TBPARTITIONS`).

Prerequisites

Note: To run ALTER TABLE on a table that contains a GSI, the MySQL version must be 5.7 or later and the PolarDB-X 1.0 version must be V5.4.1 or later.

For the full list of GSI limitations, see Notes for using GSIs.

For details on the clauses used to define GSIs, see CREATE TABLE.

Example: create a GSI after table creation

The following example creates a GSI on the buyer_id column of an existing t_order table that is sharded by order_id.

Step 1: Create the primary table.

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`);

The primary table t_order is partitioned into database shards using hash sharding on order_id. There are no table shards.

Step 2: Add the GSI.

ALTER TABLE t_order
  ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`)
  COVERING (`order_snapshot`)
  dbpartition by hash(`buyer_id`);

This creates a unique GSI named g_i_buyer on buyer_id. The index table is sharded by buyer_id. Because order_snapshot is specified as a covering column, queries that filter on buyer_id and select order_snapshot read directly from the index table without joining back to t_order. The default covering columns id (primary key) and order_id (primary table shard key) are added automatically.

Step 3: Verify the index.

Run SHOW INDEX to see all indexes, including the LSI on order_id and the GSI on buyer_id:

mysql> 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 |          0 | g_i_buyer |            1 | buyer_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | INDEX    |               |
| t_order |          1 | g_i_buyer |            2 | id             | NULL      |           0 |     NULL | NULL   |      | GLOBAL     | COVERING |               |
| t_order |          1 | g_i_buyer |            3 | order_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
| t_order |          1 | g_i_buyer |            4 | order_snapshot | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

The output shows g_i_buyer with INDEX_TYPE of GLOBAL for the index column (buyer_id) and GLOBAL COVERING for the covering columns (id, order_id, and order_snapshot).

Run SHOW GLOBAL INDEX to see only GSI-specific details, including sharding policy and status:

mysql> 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 | 0          | g_i_buyer | buyer_id    | id, order_id, order_snapshot | NULL       | buyer_id         | HASH                | 4                  |                  | NULL                | NULL               | PUBLIC |
+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

Run SHOW CREATE TABLE on the index table to inspect its schema:

mysql> SHOW CREATE TABLE g_i_buyer;

+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| g_i_buyer | CREATE TABLE `g_i_buyer` (
  `id` bigint(11) NOT NULL,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext,
  PRIMARY KEY (`id`),
  UNIQUE KEY `auto_shard_key_buyer_id` (`buyer_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`buyer_id`) |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The index table contains the primary key of the primary table, the database shard key and table shard key, the default covering columns, and the custom covering columns. It differs from the primary table in the following ways:

AUTO_INCREMENT is removed from the primary key column (id).
The LSI (l_i_order) from the primary table is not present.
A local unique index (auto_shard_key_buyer_id) is automatically created on buyer_id to enforce the global unique constraint.

PolarDB:CREATE INDEX

LSI

GSI

Syntax

Parameters

Prerequisites

Example: create a GSI after table creation

What's next