Aurora MySQL hints - Amazon Aurora
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Aurora MySQL hints

You can use SQL hints with Aurora MySQL queries to fine-tune performance. You can also use hints to prevent execution plans for important queries from changing because of unpredictable conditions.

Tip

To verify the effect that a hint has on a query, examine the query plan produced by the EXPLAIN statement. Compare the query plans with and without the hint.

In Aurora MySQL version 3, you can use all the hints that are available in community MySQL 8.0. For details about these hints, see Optimizer Hints in the MySQL Reference Manual.

The following hints are available in Aurora MySQL 2.08 and higher. These hints apply to queries that use the hash join feature in Aurora MySQL version 2, especially queries that use the parallel query optimization.

HASH_JOIN, NO_HASH_JOIN

Turns on or off the ability of the optimizer to choose whether to use the hash join optimization method for a query. HASH_JOIN lets the optimizer use hash join if that mechanism is more efficient. NO_HASH_JOIN prevents the optimizer from using hash join for the query. This hint is available in Aurora MySQL 2.08 and higher minor versions. It has no effect in Aurora MySQL version 3.

The following examples show you how to use this hint.

EXPLAIN SELECT/*+ HASH_JOIN(t2) */ f1, f2
  FROM t1, t2 WHERE t1.f1 = t2.f1;

EXPLAIN SELECT /*+ NO_HASH_JOIN(t2) */ f1, f2
  FROM t1, t2 WHERE t1.f1 = t2.f1;
HASH_JOIN_PROBING, NO_HASH_JOIN_PROBING

In a hash join query, specifies whether to use the specified table for the probe side of the join. The query tests if column values from the build table exist in the probe table, instead of reading the entire contents of the probe table. You can use HASH_JOIN_PROBING and HASH_JOIN_BUILDING to specify how hash join queries are processed without reordering the tables within the query text. This hint is available in Aurora MySQL 2.08 and higher minor versions. It has no effect in Aurora MySQL version 3.

The following examples show how to use this hint. Specifying the HASH_JOIN_PROBING hint for the table T2 has the same effect as specifying NO_HASH_JOIN_PROBING for the table T1.

EXPLAIN SELECT /*+ HASH_JOIN(t2) HASH_JOIN_PROBING(t2) */ f1, f2
  FROM t1, t2 WHERE t1.f1 = t2.f1;

EXPLAIN SELECT /*+ HASH_JOIN(t2) NO_HASH_JOIN_PROBING(t1) */ f1, f2
  FROM t1, t2 WHERE t1.f1 = t2.f1;
HASH_JOIN_BUILDING, NO_HASH_JOIN_BUILDING

In a hash join query, specifies whether to use the specified table for the build side of the join. The query processes all the rows from this table to build the list of column values to cross-reference with the other table. You can use HASH_JOIN_PROBING and HASH_JOIN_BUILDING to specify how hash join queries are processed without reordering the tables within the query text. This hint is available in Aurora MySQL 2.08 and higher minor versions. It has no effect in Aurora MySQL version 3.

The following examples show you how to use this hint. Specifying the HASH_JOIN_BUILDING hint for the table T2 has the same effect as specifying NO_HASH_JOIN_BUILDING for the table T1.

EXPLAIN SELECT /*+ HASH_JOIN(t2) HASH_JOIN_BUILDING(t2) */ f1, f2
  FROM t1, t2 WHERE t1.f1 = t2.f1;

EXPLAIN SELECT /*+ HASH_JOIN(t2) NO_HASH_JOIN_BUILDING(t1) */ f1, f2
  FROM t1, t2 WHERE t1.f1 = t2.f1;
JOIN_FIXED_ORDER

Specifies that tables in the query are joined based on the order they are listed in the query. It is useful with queries involving three or more tables. It is intended as a replacement for the MySQL STRAIGHT_JOIN hint and is equivalent to the MySQL JOIN_FIXED_ORDER hint. This hint is available in Aurora MySQL 2.08 and higher.

The following examples show you how to use this hint.

EXPLAIN SELECT /*+ JOIN_FIXED_ORDER */ f1, f2
  FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id);
JOIN_ORDER

Specifies the join order for the tables in the query. It is useful with queries involving three or more tables. It is equivalent to the MySQL JOIN_ORDER hint. This hint is available in Aurora MySQL 2.08 and higher.

The following examples show you how to use this hint.

EXPLAIN SELECT /*+ JOIN_ORDER (t4, t2, t1, t3) */ f1, f2
  FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id);
JOIN_PREFIX

Specifies the tables to put first in the join order. It is useful with queries involving three or more tables. It is equivalent to the MySQL JOIN_PREFIX hint. This hint is available in Aurora MySQL 2.08 and higher.

The following examples show you how to use this hint.

EXPLAIN SELECT /*+ JOIN_ORDER (t4, t2) */ f1, f2
  FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id);
JOIN_SUFFIX

Specifies the tables to put last in the join order. It is useful with queries involving three or more tables. It is equivalent to the MySQL JOIN_SUFFIX hint. This hint is available in Aurora MySQL 2.08 and higher.

The following examples show you how to use this hint.

EXPLAIN SELECT /*+ JOIN_ORDER (t1, t3) */ f1, f2
  FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id);

For information about using hash join queries, see Optimizing large Aurora MySQL join queries with hash joins.