PolarDB-X 1.0 supports global secondary indexes (GSIs). This topic describes how to use the INDEX HINT command to obtain query results from a specified GSI.

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-XPolarDB-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