DRDS 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 DRDS instance must be 5.4.1 or later.
  • The INDEX HINT command takes effect only for SELECT statements.

Precautions

Custom DRDS 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 DRDS hint. Otherwise, the client deletes the MySQL comment, which represents the custom DRDS hint, from the SQL statement and then sends the statement to the server for execution. As a result, the custom DRDS hint becomes invalid. For more information, see MySQL client options.

Syntax

DRDS 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