Use INDEX HINT to specify a Global Secondary Index for queries - PolarDB - Alibaba Cloud - PolarDB

PolarDB-X 1.0 supports global secondary indexes (GSIs). Use INDEX HINT to force a SELECT query to read from a specific GSI instead of letting the optimizer choose.

Limits

INDEX HINT takes effect only for SELECT statements.
The ApsaraDB RDS for MySQL instance must run MySQL 5.7 or later.
The PolarDB-X 1.0 instance must run version 5.4.1 or later.

Syntax

PolarDB-X 1.0 supports two INDEX HINT syntaxes.

Syntax	How it works	When to use
`FORCE INDEX(index_name)`	Identical to MySQL FORCE INDEX. If the named index is not a GSI, the hint is forwarded to the ApsaraDB RDS for MySQL instance.	When you want MySQL-compatible inline syntax
`/+TDDL: INDEX(table, index)/`	A TDDL hint that targets a GSI by table name (or alias) and index name within the current query block.	When you need to target a GSI explicitly with TDDL hint syntax

FORCE INDEX

tbl_name [[AS] alias] FORCE INDEX({index_name})

INDEX (TDDL hint)

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

Note The TDDL INDEX hint has no effect in these cases:

The query does not reference the specified table name or alias.
The specified GSI does not belong to the specified table.

Usage notes

PolarDB-X 1.0 hints support two comment formats:

/*+TDDL:hint_command*/
/! +TDDL:hint_command*/

When using the MySQL command-line client with the /*+TDDL:hint_command*/ format, add the -c flag to the login command. Without -c, the client strips MySQL comments from the SQL statement before sending it to the server, which causes the hint to be silently ignored.

For details, see MySQL client options.

Examples

The examples below use the following table, which has two GSIs: g_i_seller (partitioned by seller_id) and g_i_buyer (partitioned by buyer_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`)
    dbpartition by hash(`seller_id`),
  UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`)
    COVERING (`seller_id`, `order_snapshot`)
    dbpartition by hash(`buyer_id`)
    tbpartition by hash(`buyer_id`) tbpartitions 3
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  dbpartition by hash(`order_id`);

Use FORCE INDEX to target g_i_seller

Target the g_i_seller GSI in the FROM clause:

SELECT a.*, b.order_id
FROM t_seller a
  JOIN t_order b FORCE INDEX(g_i_seller) ON a.seller_id = b.seller_id
WHERE a.seller_nick = "abc";

Use the TDDL hint to target g_i_buyer

Target the g_i_buyer GSI by combining the table alias a with the index name:

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