PolarDB:INDEX HINT

Limits

• The version of the ApsaraDB RDS for MySQL instance must be 5.7 or later, and the version of the PolarDB-X 1.0 instance must be 5.4.1 or later.

• The INDEX HINT command takes effect only for SELECT statements.

Precautions

Custom PolarDB-X 1.0 hints can be in the formats of /*+TDDL:hint_command*/ and /! +TDDL:hint_command*/. If you use the /*+TDDL:hint_command*/ format, add the -c parameter to the logon command when you use the MySQL command-line client to execute an SQL statement that contains a custom PolarDB-X 1.0 hint. Otherwise, the client deletes the MySQL comment, which represents the custom PolarDB-X 1.0 hint, from the SQL statement and then sends the statement to the server for execution. As a result, the custom PolarDB-X 1.0 hint becomes invalid. For more information, see MySQL client options.

Syntax

PolarDB-X 1.0 supports the following two types of hint syntax:

• FORCE INDEX(): Its syntax is the same as that of MySQL FORCE INDEX. If the specified index is not a GSI, the FORCE INDEX hint is sent to the ApsaraDB RDS for MySQL instance for execution.

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

• INDEX(): It specifies a GSI based on the combination of the table name and index name or the combination of the table alias in the current query block and the index name.

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

  Note

  The preceding statement does not take effect in the following scenarios:

  • The query does not contain the specified table name or alias.

  • The specified GSI is not in 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`);

• Specify the g_i_seller GSI by using 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";

• Specify the g_i_buyer GSI by using the combination of the index name and table alias:

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