PolarDB:Create and use GSIs

Define a GSI at table creation

The following example creates the t_order table sharded by order_id, with g_i_seller as an inline GSI sharded by seller_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`),
  GLOBAL INDEX `g_i_seller`(`seller_id`) COVERING (`id`, `order_id`, `buyer_id`, `order_snapshot`) dbpartition BY hash(`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition BY hash(`order_id`);

Add a GSI to an existing table

The following example adds a unique GSI g_i_buyer on buyer_id with both database and table sharding:

CREATE UNIQUE GLOBAL INDEX `g_i_buyer` ON `t_order`(`buyer_id`)
    COVERING(`seller_id`, `order_snapshot`)
    dbpartition BY hash(`buyer_id`) tbpartition BY hash(`buyer_id`) tbpartitions 3

After the GSI is created, PolarDB-X verifies the index data before the DDL statement completes. To manually verify or repair index data, run CHECK GLOBAL INDEX.

Automatic index selection

PolarDB-X automatically selects the lowest-cost covering index for queries on tables with GSIs. Use EXPLAIN to confirm which index the optimizer selects:

EXPLAIN SELECT t_order.id, t_order.order_snapshot FROM t_order WHERE t_order.seller_id = 's1';

Execution plan result:

IndexScan(tables="g_i_seller_sfL1_2", sql="SELECT `id`, `order_snapshot` FROM `g_i_seller` AS `g_i_seller` WHERE (`seller_id` = ?)")

The optimizer selects g_i_seller because all queried columns (id, order_snapshot, and seller_id) are covered by the index, and the seller_id equality condition matches the index shard key — eliminating both a table lookup and a full partition scan.

If a query needs columns not in the index, PolarDB-X first queries the GSI for primary keys and base table shard keys, then fetches the missing columns from the base table. For details, see INDEX HINT.

Force a specific index

Use either of the following syntaxes to override automatic index selection:

FORCE INDEX:

FORCE INDEX({index_name})

Example:

SELECT a.order_id FROM t_order a FORCE INDEX(g_i_seller) WHERE a.buyer_id = 123;

TDDL hint:

/*+TDDL:INDEX({table_name/table_alias}, {index_name})*/

Example:

/*+TDDL:index(a, g_i_buyer)*/ SELECT * FROM t_order a WHERE a.buyer_id = 123

Ignore or suggest an index

IGNORE INDEX — exclude a specific index from consideration:

IGNORE INDEX({index_name},...)

Example:

SELECT t_order.id, t_order.order_snapshot FROM t_order IGNORE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';

USE INDEX — suggest a preferred index:

USE INDEX({index_name},...)

Example:

SELECT t_order.id, t_order.order_snapshot FROM t_order USE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';

Limitations on creating GSIs

• GSIs cannot be created on non-partitioned tables or broadcast tables.

• Unique GSIs do not support prefix indexes.

• An index table must have an explicit name.

• An index table must specify a database sharding rule, or both a database and table sharding rule. Specifying only a table sharding rule is not supported.

• The index keys of an index table must include all shard keys of the index table.

• The same column cannot serve as both an index key column and a covering column.

• By default, an index table contains the primary key columns and all shard key columns of the base table. Columns not specified as index key columns are used as covering columns.

• In DRDS mode: if all index columns of a local index in the base table are present in the index table, the local index is automatically added to the index table.

• If no local indexes exist on the index key columns of a GSI, a local index is automatically created for each index key column.

• A GSI created on multiple columns automatically gets a composite index covering all index key columns.

• The Length parameter only specifies the prefix length of a sharding key for creating a local index.

Limitations on ALTER TABLE

The following matrix shows which ALTER TABLE column operations are supported for tables with GSIs.

Supported\*: Only for instances that support lock-free column type changes.

Additional notes on ALTER TABLE:

• To rename a GSI, drop it with DROP INDEX and recreate it. Renaming a GSI directly can degrade its performance. For assistance, contact us.

• When a column belongs to multiple column types in the matrix above and an operation is unsupported for any one of those types, the operation cannot proceed.

The following table lists index management operations via ALTER TABLE:

Limitations on ALTER GSI TABLE

• DDL and DML statements cannot be executed directly on a GSI.

• DML statements with NODE HINTs cannot update the base table and GSIs simultaneously.

Other supported statements

The following statements work on tables that have GSIs:

Why do I get "Does not support create Global Secondary Index on single or broadcast table"?

This error means the target table is a non-partitioned table or a broadcast table, which cannot have GSIs.

Check how the table was created:

• Non-partitioned table (DRDS mode): Created in a single database with no sharding. See CREATE TABLE (DRDS mode).

• Non-partitioned table (AUTO mode): Created with the SINGLE keyword. See CREATE TABLE (AUTO mode).

• Broadcast table: Created with the BROADCAST keyword; an identical copy of the table exists on each data node. See CREATE TABLE (DRDS mode) or CREATE TABLE (AUTO mode).

GSIs can only be created on partitioned, non-broadcast tables.