PolarDB:Global Secondary Indexes (GSIs)

Why local indexes fall short

A local index is partitioned the same way as its table. Each index partition covers only the corresponding table partition, so data is sorted within each partition but not across them. This creates three problems when queries don't include the partition key:

• Full partition scans: Every partition is scanned, causing read amplification that grows with partition count.

• No guaranteed global order: Results from sorted partitions are not globally sorted, which may trigger an additional sorting step.

• Unique index restriction: A local unique index must include all partition keys to enforce uniqueness across partitions.

A GSI is not partitioned. It is built from data across all partitions and is globally sorted. A globally unique index on a GSI does not need to include all partition keys.

When to use a GSI

Prerequisites

Before you begin, make sure you have:

• A PolarDB for MySQL cluster running version 8.0.2, revision 8.0.2.2.7 or later — see Query the version number

Limitations

• GSIs are supported only on InnoDB partitioned tables. Hybrid partitioned tables are not supported.

• A GSI cannot be a full-text index or a spatial index.

• GSIs are not supported on compressed tables, temporary tables, or encrypted tables. Tables using the REDUNDANT or COMPRESSED row format are also not supported.

• Tables with GSIs do not support generated columns.

• Partition-level DDL operations (except adding new RANGE or LIST partitions) invalidate existing GSIs. Delete and rebuild the affected GSIs, or use the UPDATE GLOBAL INDEX syntax to rebuild them in the same statement.

Feature enhancements

• Create GSIs faster using parallel DDL.

• Use instant add column on partitioned tables that have GSIs.

• For RANGE or LIST partitioned tables with GSIs, partition-level metadata locks (MDLs) are supported when adding new partitions.

• Convert a table with GSIs to an INTERVAL RANGE partitioned table, or create GSIs directly on an INTERVAL RANGE partitioned table.

• When you perform partition-level DDL operations on a partitioned table that has GSIs, use the UPDATE GLOBAL INDEX syntax to rebuild the GSIs in the same statement.

Syntax

Add the GLOBAL or LOCAL keyword after the index name when creating an index.

If you omit the keyword, a local index is created by default.

-- Create a global index
INDEX index_name(column) GLOBAL

-- Create a local index
INDEX index_name(column) LOCAL

Create a GSI

CREATE TABLE t1(
  a INT PRIMARY KEY,
  b INT,
  INDEX k1(b) GLOBAL
) PARTITION BY HASH(a) PARTITIONS 3;

-- Create the table
CREATE TABLE t1(
  a INT PRIMARY KEY,
  b INT
) PARTITION BY HASH(a) PARTITIONS 3;

-- Add a global index on column b
ALTER TABLE t1 ADD INDEX k1(b) GLOBAL;

-- Add a globally unique index on column b
CREATE UNIQUE INDEX k2 ON t1(b) GLOBAL;

-- Step 1: Create a range-partitioned table with a global index
CREATE TABLE t1(
  a INT PRIMARY KEY,
  b INT,
  INDEX k1(b) GLOBAL
) PARTITION BY RANGE (`a`)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB);

-- Step 2: Drop the p1 partition and rebuild the GSI
ALTER TABLE t1 DROP PARTITION p1 UPDATE GLOBAL INDEX;

Performance

GSIs outperform local indexes for SELECT, UPDATE, and DELETE queries that don't include the partition key. The advantage grows as partition count and data volume increase.

Test setup: Two HASH-partitioned tables with 32 partitions and 1,000,000 rows each — one with a local index, one with a GSI.

-- Table with local index
CREATE TABLE mytest1.big_table_1(
  a INT PRIMARY KEY,
  b INT,
  c INT,
  INDEX k1(b) LOCAL
) PARTITION BY HASH(a) PARTITIONS 32;

-- Table with GSI
CREATE TABLE mytest2.big_table_1(
  a INT PRIMARY KEY,
  b INT,
  c INT,
  INDEX k1(b) GLOBAL
) PARTITION BY HASH(a) PARTITIONS 32;

SELECT — execution time, query condition does not include the partition key:

UPDATE — execution time, query condition does not include the partition key:

DELETE — execution time, query condition does not include the partition key:

The performance advantage of GSIs is more pronounced as data volume and partition count increase.

What's next

• Parallel DDL — speed up GSI creation on large tables

• Instant add column — add columns to partitioned tables with GSIs without a full table rebuild

• INTERVAL RANGE partitioned tables — automatic partition management compatible with GSIs