Hints

As well as adjusting the cost constants in the mysql system database, MySQL also provides a method to override how plans are selected. Using query hints is useful for two distinct reasons:

1. In debugging. EXPLAIN shows a decision that was made based on the metadata that was available. Adding a hint allows you to force some other execution plans and compare the actual execution time.
2. In production. If while debugging you notice that an alternative was sufficiently faster, you may choose to add a hint to speed up execution.

I would say that while I frequently use hints in debugging, I am always careful when introducing a hint in production. These can create a maintenance burden because as data distribution and indexes change over time, hints can lock you into a specific plan chosen at one point in time.

Best practice suggests that you should always revisit hints after a major MySQL version upgrade (for example, when upgrading to MySQL 9.0) [1]. You may find that as new optimizations are introduced, you are able to retire a number of your hints.

Old Style Hints

Earlier versions of MySQL supported only a series of hints that were an extension of SQL syntax directly. For example:

# Join tables in the order specified
SELECT STRAIGHT_JOIN Country.Name as CountryName, City.Name AS City
FROM Country INNER JOIN City ON City.CountryCode=Country.Code;

# Force usage of an index
SELECT * FROM Country FORCE INDEX (p)
WHERE continent='Asia' and population > 5000000;

# Ignore an index
SELECT * FROM Country IGNORE INDEX (p)
WHERE continent='Asia' and population > 5000000;

# Suggest an index over other indexes
SELECT * FROM Country USE INDEX (p)
WHERE continent='Asia' and population > 5000000;

While these hints continue to be supported in MySQL 8.0, they are partially replaced by comment-style hints. In Example 5 we can see that the p (population) index avoided in Example 2 is being selected due to the use of a FORCE INDEX hint. Despite the FORCE INDEX, EXPLAIN still shows its true cost as 152.21 versus the table scan cost of approximately 53.

Example 5: Forcing the index to be used despite its cost

EXPLAIN FORMAT=JSON
SELECT * FROM Country FORCE INDEX (p) WHERE continent='Asia' and population > 5000000;
{
  "query_block": {
     "select_id": 1,
     "cost_info": {
     "query_cost": "152.21"   # Cost is greater than table scan
     },
     "table": {
     "table_name": "Country",
     "access_type": "range",
     "possible_keys": [
     "p"
     ],
     "key": "p",
     "used_key_parts": [
     "Population"
     ],
     "key_length": "4",
     "rows_examined_per_scan": 108,
     "rows_produced_per_join": 15,
     "filtered": "14.29",
     "index_condition": "(`world`.`Country`.`Population` > 5000000)",
     "cost_info": {
     "read_cost": "149.12",
     "eval_cost": "3.09",
     "prefix_cost": "152.21",
     "data_read_per_join": "3K"
     },
     "used_columns": [
     "Code",
     "Name",
     "Continent",
     "Region",
     "SurfaceArea",
     "IndepYear",
     "Population",
     "LifeExpectancy",
     "GNP",
     "GNPOld",
     "LocalName",
     "GovernmentForm",
     "HeadOfState",
     "Capital",
     "Code2"
     ],
     "attached_condition": "(`world`.`Country`.`Continent` = 'Asia')"
     }
  }
}

New Comment-Style Hints

MySQL 8.0 extends the new style of comment-style hints introduced in MySQL 5.7, with the ability to control the join order of tables (similar to STRAIGHT_JOIN). I prefer these hints over the old SQL-syntax-extension hints for three reasons:

1. They are easy to read and write by separating SQL’s declarative nature from something that is implying how to execute.
2. They have clear semantics as hints and not directives. This means that if a hint is unable to operate it will result in a statement warning and not an error. This contrasts with hints like FORCE INDEX, where a missing index will result in an error. This behavior is similar to hints in the Oracle database, for example.
3. They are more fine grained in control. This grants the DBA more flexibility in hint usage.

Hint Name	Description
BKA, NO_BKA	Enable or disable the Batched Key Access optimization for specific tables (this optimization is off by default, but can be controlled by the optimizer_switch).
BNL, NO_BNL	Enable or disable the Block Nested Loop optimization for specified tables.
MAX_EXECUTION_TIME	Set a maximum execution time for the query to run in milliseconds. This hint currently only affects SELECT statements.
MRR, NO_MRR	Effects Multi-Range Read optimization
NO_ICP	Effects Index Condition Pushdown optimization
NO_RANGE_OPTIMIZATION	Disables range optimization for the specified tables or indexes.
QB_NAME	Assign a name to query block
SEMIJOIN, NO_SEMIJOIN	Control semi-join strategy for a subquery (options include DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION).
SUBQUERY	Similar to SEMIJOIN/NO_SEMIJOIN. Allows control of subquery strategy, including IN-to-EXISTS optimizations.

In Example 6, range optimization has been disabled for a specific table. This results in the p (Population) index being ignored, despite being extremely selective. There are only two rows in the table with a population >1 Billion.

Example 6: Disabling range optimization means the index cannot be used

EXPLAIN FORMAT=JSON
SELECT /*+NO_RANGE_OPTIMIZATION(Country) */  * FROM Country
WHERE Population > 1000000000 AND Continent='Asia';
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "56.80"
    },
    "table": {
      "table_name": "Country",
      "access_type": "ALL",       # The access method is a table scan
      "possible_keys": [          # The possible key can only be used as a
        "p"                       # range scan which has been disabled
      ],
      "rows_examined_per_scan": 239,
      "rows_produced_per_join": 11,
      "filtered": "4.76",
      "cost_info": {
        "read_cost": "54.52",
        "eval_cost": "2.28",
        "prefix_cost": "56.80",
        "data_read_per_join": "2K"
      },
      "used_columns": [
        "Code",
        "Name",
        "Continent",
        "Region",
        "SurfaceArea",
        "IndepYear",
        "Population",
        "LifeExpectancy",
        "GNP",
        "GNPOld",
        "LocalName",
        "GovernmentForm",
        "HeadOfState",
        "Capital",
        "Code2"
      ],
      "attached_condition": "((`world`.`Country`.`Continent` = 'Asia') and (`world`.`Country`.`Population` > 1000000000))"
    }
  }
}

[1]	http://mysqlserverteam.com/what-to-do-with-optimizer-hints-after-an-upgrade/