INDEX HINT - PolarDB - Alibaba Cloud Documentation Center

PolarDB-X supports global secondary indexes (GSIs). Use INDEX hints to route a query to a specific GSI rather than letting the optimizer choose automatically.

Limitations

INDEX hints are supported only in SELECT statements.

Syntax

PolarDB-X provides two INDEX hint syntaxes:

FORCE INDEX

The FORCE INDEX syntax follows the MySQL FORCE INDEX standard. Write it inline in the FROM clause:

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

If the specified index is not a GSI, PolarDB-X forwards the hint to the MySQL storage engine.

INDEX() in a TDDL hint

The INDEX() hint is a PolarDB-X custom hint written in the /*+TDDL: ... */ comment format. It identifies the target GSI by combining a table name (or alias) with an index name:

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

The hint has no effect in the following cases:

The specified table name or alias does not exist.
The specified index is not a GSI of that table.

Usage notes

The `-c` option when using the MySQL command-line client

When writing TDDL hints in the /*+TDDL:hint_command*/ format, the MySQL command-line client strips comment-style hints by default before sending the statement to the server. To prevent this, pass the -c option when starting the client:

mysql -h <host> -P <port> -u <user> -p -c

Without -c, the hint is removed and does not take effect. For details, see mysql Client Options on the MySQL documentation site.

The /!+TDDL:hint_command*/ format is an alternative that is not treated as a comment by the MySQL client and does not require the -c option.

Examples

The examples below use the following table, which has two GSIs: g_i_seller (partitioned by seller_id) and g_i_buyer (a unique GSI 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 in a JOIN

Specify the g_i_seller GSI with FORCE INDEX 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 INDEX() with a table alias

Specify the g_i_buyer GSI using the table alias a:

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