FORCE INDEX Behavior across Global and Local Indexes - PolarDB-X

PolarDB-X extends MySQL's FORCE INDEX and USE INDEX with two-level index support, letting you specify both a global secondary index (GSI) and a local index in a single hint.

Background

PolarDB-X supports two types of indexes:

Global secondary index (GSI): A distributed index partitioned across nodes.
Local index: An index that lives on a single table shard or as an index partition of a GSI.

Because a GSI itself contains local index partitions, you can target both levels in one hint — something MySQL's single-level FORCE INDEX cannot express.

Version support

Two-level index hints require a minimum compute node version. Single-level hints work on all versions.

Compute node version	Two-level index support
Earlier than 5.4.18	Not supported
5.4.18, minor version < 1718157	Not supported
5.4.18, minor version >= 1718157	Supported
5.4.19, release date before July 10, 2024	Not supported
5.4.19, release date on or after July 10, 2024	Supported
Later than 5.4.19	Supported

To check your instance version, see View and update the version of an instance. For version history, see Release notes.

Differences from MySQL

Behavior	MySQL	PolarDB-X
Index name match	—	Full name required; prefix match not supported
Two-level index hints	Not supported	Supported (GSI + local index)
`FORCE INDEX (PRIMARY)`	—	Disables all GSIs

Usage notes

Use the full index name. Prefix match is not supported.
Local index selection is conditional. A specified local index is forced only when its parent GSI is selected by the optimizer based on cost estimation. GSIs that do not contain the specified local index partition are excluded from consideration.
FORCE INDEX (PRIMARY) disables all GSIs. When you specify the primary key, the optimizer does not consider any global secondary index.

Syntax

PolarDB-X supports two notations that produce the same result:

FORCE INDEX — inline SQL syntax, compatible with MySQL clients
INDEX HINT — a TDDL optimizer hint embedded in a SQL comment

Single-level index

Specify a GSI, a local index, or the primary key. The optimizer selects a single index.

FORCE INDEX

tbl_name [[AS] alias] FORCE INDEX({gsi_name | local_index_name | primary})

INDEX HINT

/*+TDDL: INDEX({table_name | table_alias}, {gsi_name | local_index_name | primary})*/

Two-level index

Target both a GSI and one of its local index partitions in a single hint.

FORCE INDEX

tbl_name [[AS] alias] FORCE INDEX(gsi_name[.{local_index_name | primary}] | local_index_name | primary[.{local_index_name | primary}])

INDEX HINT

/*+TDDL: INDEX({table_name | table_alias}, {gsi_name | local_index_name | primary} [, {local_index_name | primary}])*/

Parameters

Parameter	Description
`gsi_name`	Name of the global secondary index
`local_index_name`	Name of the local index
`primary`	The primary key

Examples

All examples use the following table, which has two GSIs (g_i_buyer, g_i_seller) and two local indexes (l_i_order, l_i_buyer):

CREATE TABLE `t_order` (
    `id` bigint(11) NOT NULL AUTO_INCREMENT,
    `order_id` varchar(20) NOT NULL,
    `buyer_id` varchar(20) DEFAULT NULL,
    `seller_id` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`),
    GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_id`)
        PARTITION BY KEY(`buyer_id`)
        PARTITIONS 16,
    GLOBAL INDEX `g_i_seller` (`seller_id`) COVERING (`order_id`)
        PARTITION BY KEY(`seller_id`)
        PARTITIONS 16,
    KEY `l_i_order` (`order_id`),
    KEY `l_i_buyer` (`buyer_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`order_id`)
PARTITIONS 16

Single-level: force a local index with `FORCE INDEX`

Force the local index l_i_order:

mysql> EXPLAIN SELECT * FROM T_ORDER FORCE INDEX(l_i_order);
+---------------------+
| LOGICAL EXECUTIONPLAN
+---------------------+
| Gather(concurrent=true)
|   LogicalView(tables="t_order[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `buyer_id`, `seller_id` FROM `T_ORDER` AS `T_ORDER` FORCE INDEX(L_I_ORDER)")
| HitCache:false
| Source:PLAN_CACHE
| TemplateId: 3d1b35fa
+---------------------+

The plan shows a LogicalView scanning all 16 shards with FORCE INDEX(L_I_ORDER) pushed down to each shard.

Single-level: force a GSI with `INDEX HINT`

Force the GSI g_i_buyer using the TDDL hint:

mysql> EXPLAIN /*TDDL:INDEX(T_ORDER, g_i_buyer)*/ SELECT * FROM T_ORDER;
+---------------------+
| LOGICAL EXECUTIONPLAN
+---------------------+
| Project(id="id", order_id="order_id", buyer_id="buyer_id", seller_id="seller_id")
|   BKAJoin(condition="id = id AND order_id = order_id", type="inner")
|     Gather(concurrent=true)
|       IndexScan(tables="g_i_buyer_$27c2[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `buyer_id` FROM `g_i_buyer_$27c2` AS `g_i_buyer_$27c2`")
|     Gather(concurrent=true)
|       LogicalView(tables="t_order[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `seller_id` FROM `T_ORDER` AS `T_ORDER` FORCE INDEX(PRIMARY) WHERE (((`id`, `order_id`)) IN (...))")
| HitCache:false
| Source:null
| TemplateId: NULL
+---------------------+

The plan uses IndexScan on the GSI, then a BKAJoin to fetch remaining columns from the base table.

Two-level: force a local index partition of a GSI with `FORCE INDEX`

Force auto_shard_key_buyer_id, which is a local index partition inside g_i_buyer:

mysql> EXPLAIN SELECT * FROM T_ORDER FORCE INDEX(g_i_buyer.auto_shard_key_buyer_id);
+---------------------+
| LOGICAL EXECUTIONPLAN
+---------------------+
| Project(id="id", order_id="order_id", buyer_id="buyer_id", seller_id="seller_id")
|   BKAJoin(condition="id = id AND order_id = order_id", type="inner")
|     Gather(concurrent=true)
|       IndexScan(tables="g_i_buyer_$27c2[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `buyer_id` FROM `g_i_buyer_$27c2` AS `g_i_buyer_$27c2` FORCE INDEX(AUTO_SHARD_KEY_BUYER_ID)")
|     Gather(concurrent=true)
|       LogicalView(tables="t_order[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `seller_id` FROM `T_ORDER` AS `T_ORDER` FORCE INDEX(PRIMARY) WHERE (((`id`, `order_id`)) IN (...))")
| HitCache:false
| Source:PLAN_CACHE
| TemplateId: ae278c70
+---------------------+

The IndexScan on g_i_buyer now includes FORCE INDEX(AUTO_SHARD_KEY_BUYER_ID), targeting the specific local index partition within the GSI.

Two-level: multiple `INDEX HINT` for a self-join

Apply different two-level index hints to two aliases of the same table (t1 and t2):

t1: force local index auto_shard_key_seller_id inside GSI g_i_seller
t2: force local index L_I_ORDER on the table nicknamed t2

mysql> EXPLAIN /*TDDL:index(t1, g_i_seller, auto_shard_key_seller_id) index(t2, primary, L_I_ORDER)*/ SELECT * FROM t_order t1 join t_order t2 on t1.id=t2.id where t1.buyer_id = '123456';
+---------------------+
| LOGICAL EXECUTIONPLAN
+---------------------+
| BKAJoin(condition="id = id", type="inner")
|   Project(id="id", order_id="order_id", buyer_id="buyer_id", seller_id="seller_id")
|     BKAJoin(condition="id = id AND order_id = order_id", type="inner")
|       Gather(concurrent=true)
|         IndexScan(tables="g_i_seller_$3e3d[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `seller_id` FROM `g_i_seller_$3e3d` AS `g_i_seller_$3e3d` FORCE INDEX(AUTO_SHARD_KEY_SELLER_ID)")
|       Gather(concurrent=true)
|         LogicalView(tables="t_order[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `buyer_id` FROM `t_order` AS `t_order` FORCE INDEX(PRIMARY) WHERE ((`buyer_id` = ?) AND (((`id`, `order_id`)) IN (...)))")
|   Gather(concurrent=true)
|     LogicalView(tables="t_order[p1,p2,p3,...p16]", shardCount=16, sql="SELECT `id`, `order_id`, `buyer_id`, `seller_id` FROM `t_order` AS `t_order` FORCE INDEX(L_I_ORDER) WHERE (`id` IN (...))")
| HitCache:false
| Source:null
| TemplateId: NULL
+---------------------+

Each INDEX HINT in the comment applies independently to its named table alias.