Implement join order hints

Join-order hints affect the order in which the optimizer joins tables.

Syntax of the JOIN_FIXED_ORDER hint:

hint_name([@query_block_name])

Syntax of other join-order hints:

hint_name([@query_block_name] tbl_name [, tbl_name] ...)

hint_name(tbl_name[@query_block_name] [, tbl_name[@query_block_name]] ...)

The syntax refers to these terms:

• hint_name: These hint names are permitted:
• JOIN_FIXED_ORDER: Force the optimizer to join tables using the order in which they appear in the FROM clause. This is the same as specifying SELECT STRAIGHT_JOIN.
• JOIN_ORDER: Instruct the optimizer to join tables using the specified table order. The hint applies to the named tables. The optimizer may place tables that are not named anywhere in the join order, including between specified tables.
• JOIN_PREFIX: Instruct the optimizer to join tables using the specified table order for the first tables of the join execution plan. The hint applies to the named tables. The optimizer places all other tables after the named tables.
• JOIN_SUFFIX: Instruct the optimizer to join tables using the specified table order for the last tables of the join execution plan. The hint applies to the named tables. The optimizer places all other tables before the named tables.

• tbl_name: The name of a table used in the statement. A hint that names tables applies to all tables that it names. The JOIN_FIXED_ORDER hint names no tables and applies to all tables in the FROM clause of the query block in which it occurs.

If a table has an alias, hints must refer to the alias, not the table name.

Table names in hints cannot be qualified with schema names.

• query_block_name: The query block to which the hint applies. If the hint includes no leading @query_block_name, the hint applies to the query block in which it occurs. For tbl_name@query_block_name syntax, the hint applies to the named table in the named query block. To assign a name to a query block, see Optimizer Hints for Naming Query Blocks.

Example:

SELECT

/*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)

    JOIN_ORDER(t4@subq1, t3)

    JOIN_SUFFIX(t1) */

COUNT(*) FROM t1 JOIN t2 JOIN t3

           WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)

             AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);

For other details refer to the MySQL documentation.