How to use INDEX hints

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