PolarDB:Introduction to hints

How SQL hints work

SQL hints are embedded in SQL comments and processed by PolarDB-X 1.0 before execution. A hint is a string placed inside /* and */ (or /! and */) that begins with +TDDL:, followed by one or more hint commands:

/*+TDDL: hint_command [hint_command ...]*/
/!+TDDL: hint_command [hint_command ...]*/

Each hint_command targets a specific behavior — routing to a database shard, enabling read/write splitting, setting a timeout, and so on. Separate multiple hint commands with spaces.

Examples

-- Query the names of physical tables in each database shard.
/*+TDDL:scan()*/SHOW TABLES;

-- Route the query to database shard 0000 of a read-only ApsaraDB RDS instance.
/*+TDDL:node(0) slave()*/SELECT * FROM t1;

In the examples, /*+TDDL:scan()*/ and /*+TDDL:node(0) slave()*/ are custom hints. The two hints begin with +TDDL:. scan(), node(0), and slave() are hint commands and are separated with spaces.

Syntax formats

PolarDB-X 1.0 supports two syntax formats:

Why the `-c` flag matters

The /*...*/ format is treated as a standard MySQL comment. By default, the MySQL command-line client strips comments before sending the SQL statement to the server, which causes the hint to be ignored. Add -c to your login command to preserve hints:

mysql -h <host> -P <port> -u <username> -p -c

See MySQL comments and mysql client options for details.

Where to place a hint in a SQL statement

PolarDB-X 1.0 supports hints in Data Manipulation Language (DML), Data Definition Language (DDL), and Data Access Language (DAL) statements.

Place the hint either before the statement or after the first keyword:

-- Before the statement
/*+TDDL: ... */ SELECT ...
/*+TDDL: ... */ INSERT ...
/*+TDDL: ... */ REPLACE ...
/*+TDDL: ... */ UPDATE ...
/*+TDDL: ... */ DELETE ...
/*+TDDL: ... */ CREATE TABLE ...
/*+TDDL: ... */ ALTER TABLE ...
/*+TDDL: ... */ DROP TABLE ...
/*+TDDL: ... */ SHOW ...

-- After the first keyword
SELECT /*+TDDL: ... */ ...
INSERT /*+TDDL: ... */ ...
REPLACE /*+TDDL: ... */ ...
UPDATE /*+TDDL: ... */ ...
DELETE /*+TDDL: ... */ ...

Different hints apply to different statement types. Check the topic for each hint command to confirm which statements it supports.

Limits on using multiple hints

Combine multiple hint commands inside a single hint — do not use separate hints in the same statement.

-- Correct: two commands in one hint
SELECT /*+TDDL:node(0) slave()*/ * FROM orders;

-- Incorrect: two separate hints in one statement
SELECT /*+TDDL:node(0)*/ /*+TDDL:slave()*/ * FROM orders;

-- Incorrect: duplicate commands in one hint
SELECT /*+TDDL:node(0) node(1)*/ * FROM orders;

Available hint categories

PolarDB-X 1.0 custom SQL hints fall into four categories. Each category targets a different control point in query execution:

• Read/write splitting — Route reads to read-only nodes.

• Specify a custom timeout for a SQL statement — Override the default execution timeout.

• Specify database shards for execution — Route a statement to one or more specific database shards.

• Scan table shards across database shards — Query across all or selected table shards and database shards.

Troubleshooting

Hints are ignored

The most common cause is the MySQL command-line client stripping /*...*/ comments. Switch to the /!+TDDL:...*/ format, or reconnect with the -c flag:

mysql -h <host> -P <port> -u <username> -p -c

What's next

• Read/write splitting

• Specify a custom timeout for a SQL statement

• Specify database shards for execution

• Scan table shards across database shards