PolarDB-X supports global secondary indexes. You can use INDEX hints to query data from a specified global secondary index.

Limits

You can use INDEX hints only in SELECT statements.

Note

PolarDB-X support custom hints in the /*+TDDL:hint_command*/ and /*+TDDL:hint_command*/ formats. If you use the /*+TDDL:hint_command*/ format, you must specify the -c option in the logon command when you use the MySQL command-line client to execute an SQL statement that contains a custom PolarDB-X hint. If you do not specify this option, the client deletes the custom hint from the statement before it sends the statement to the PolarDB-X server to be executed because the custom hint is defined in the format of comments in the SQL statement. As a result, the custom PolarDB-X hint cannot take effect. For more information, see mysql Client Options on the official website of MySQL.

Syntax

PolarDB-X supports the following types of INDEX hint syntax:

  • FORCE INDEX(): The syntax of FORCE INDEX() is the same as that of MySQL FORCE INDEX. If the specified index in a hint is not a global secondary index, the FORCE INDEX hint is sent to the MySQL engine to be executed. 
    # FORCE INDEX()
tbl_name [[AS] alias] [index_hint]
index_hint:
    FORCE INDEX({index_name})
  • INDEX(): If you use INDEX() to define a hint, a global secondary index is specified based on the combination of the specified table name and index name or the combination of the specified table alias and index name. 
    # INDEX()
/*+TDDL:
    INDEX({table_name | table_alias}, {index_name})
*/
    Note A hint statement cannot take effect in the following scenarios:
    • The specified table name or alias does not exist.
    • The specified index is not a global secondary index of the specified table.

Examples

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`);
  • In the FROM clause, use FORCE INDEX to specify the g_i_seller global secondary index.
    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 to specify the g_i_buyer global secondary index by specifying the table alias.
    /*+TDDL:index(a, g_i_buyer)*/ SELECT * FROM t_order a WHERE a.buyer_id = 123