In a distributed database, queries that filter on an index column but return non-indexed columns require a table lookup — fetching data from the base table after finding the matching index entries. This extra round trip adds network I/O and increases query latency.
A clustered index eliminates this overhead. It is a special type of global secondary index (GSI) that includes all columns of the base table. Queries served from a clustered index retrieve all required columns without scanning the base table, reducing both I/O and query latency.
PolarDB-X automatically manages column coverage and keeps the clustered index in sync with the base table.
Prerequisites
Before you begin, ensure that you have:
A PolarDB-X instance running kernel version V5.4.9 or later
How it works
When you add the CLUSTERED keyword to an index definition, PolarDB-X creates an index table that mirrors all columns of the base table. Reads go directly to this index table, skipping the base table scan. Any column updates in the base table are automatically propagated to the clustered index. Local indexes on the base table are also synchronized to the clustered index table.
Create a clustered index
Add the CLUSTERED keyword to any index definition. PolarDB-X supports three methods.
Create a table with a clustered index
CREATE [SHADOW] TABLE [IF NOT EXISTS] tbl_name
(create_definition, ...)
[table_options]
[drds_partition_options]
create_definition:
[UNIQUE] CLUSTERED INDEX index_name [index_type] (index_col_name,...)
[drds_partition_options]
[index_option] ...If the table uses its primary key as the shard key, omit the sharding rule from [drds_partition_options].Add a clustered index to an existing table
Using CREATE INDEX:
CREATE [UNIQUE]
CLUSTERED INDEX index_name [index_type]
ON tbl_name (index_col_name,...)
[drds_partition_options]
[index_option] ...If the table uses its primary key as the shard key, omit the sharding rule from [drds_partition_options].Using ALTER TABLE:
ALTER TABLE tbl_name
alter_specification
alter_specification:
| ADD [UNIQUE] CLUSTERED {INDEX|KEY} index_name
[index_type] (index_col_name,...)
[drds_partition_options]
[index_option] ...When using ALTER TABLE:Specify only one sharding rule in the alter_specification clause.Always provide a name for the clustered index.
If the table uses its primary key as the shard key, omit the sharding rule from [drds_partition_options].Example
The following example creates a partition table, adds a clustered index on seller_id and x, and then verifies the result.
Create the base table:
CREATE PARTITION TABLE `t_order` (
`t` timestamp null default CURRENT_TIMESTAMP,
`x` int default 3,
`order_id` varchar(20) DEFAULT NULL,
`seller_id` varchar(20) DEFAULT NULL
);Create the clustered index:
CREATE CLUSTERED INDEX `c_i` ON `t_order` (seller_id, x)Verify the base table schema:
SHOW CREATE TABLE t_order;The output shows both the local key and the clustered index entry:
+---------+-----------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------+
| t_order | CREATE PARTITION TABLE `t_order` (
`t` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`x` int(11) DEFAULT '3',
`order_id` varchar(20) DEFAULT NULL,
`seller_id` varchar(20) DEFAULT NULL,
LOCAL KEY `_local_c_i` (`seller_id`, `x`),
CLUSTERED INDEX `c_i`(`seller_id`, `x`) DBPARTITION BY HASH(`seller_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 |
+---------+-----------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)Verify the clustered index schema:
SHOW CREATE TABLE c_i;The output confirms that the clustered index contains all columns of the base table:
+-------+---------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------+
| c_i | CREATE TABLE `c_i` (
`t` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`x` int(11) DEFAULT '3',
`order_id` varchar(20) DEFAULT NULL,
`seller_id` varchar(20) DEFAULT NULL,
KEY `auto_shard_key_seller_id` USING BTREE (`seller_id`),
KEY `i_seller_id_x` USING BTREE (`seller_id`, `x`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`seller_id`) |
+-------+---------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)What's next
For a full list of operations and limits that apply to clustered indexes, see GSI.