The MySQL optimizer has a well known special system variable—optimizer_switch—which allows control of various optimizer modes such as index condition pushdown (ICP), batched key access, etc. One big disadvantage of using the optimizer_switch, however, is that changing its value is an extra step in the query execution. Another disadvantage of the optimizer_switch is that it affects the entire statement whereas sometimes it’s desirable or necessary to change only the behavior of a specific table or query block. In order to address these issues and improve usability, new hints were added to allow for greater and more fine-grained control of optimizer behavior directly within SQL statements.

A few key points regarding the new hints:

• Comment syntax /*+ */ is used for the new hints.
• Multiple hints can be specified within the same comment.
• A query block can have only one comment containing hints, and that comment must directly follow the SELECT, UPDATE, INSERT, REPLACE, or DELETE keyword.
• Incorrect and/or invalid syntax within a hint will cause a warning.
• The first conflicting hint will take effect, while subsequent conflicting/duplicate hints are ignored with a warning.
• Multilevel hints are supported. For example, a hint can affect only a specific table or an individual query block.

The following hints were added in MySQL 5.7.7:

• BKA, NO_BKA — control use of the Batched Key Access algorithm for a particular table or query block.
• BNL, NO_BNL — control use of the Block Nested-Loop algorithm for a particular table or query block.
• MRR, NO_MRR — control the Multi-Range Read strategy for a particular index or table.
• NO_ICP — disables the use of index condition pushdown for a particular index or table.
• NO_RANGE_OPTIMIZATION — disables the use of the range access for a particular index or table.
• MAX_EXECUTION_TIME — sets the statement execution timeout at N milliseconds.
• QB_NAME — an auxiliary hint for naming a particular query block. This name can then be used in the later hint specification in order to greater simplify the use of hints in complex compound statements.

In MySQL 5.7.8, Oystein Grovlen added hints for controlling the subquery optimizer strategies:

• SEMIJOIN, NO_SEMIJOIN — enable or disable the named semi-join strategy.
• SUBQUERY — affects whether to use subquery materialization or IN-to-EXISTS transformations.

For additional details on the new hint work, please see the new section within the manual.

I hope that you find the new hint work helpful! If you have any questions or encounter any problems with the new hints, please let us know here in the comments, open a bug report at bugs.mysql.com, or open a support ticket.

THANK YOU for using MySQL!