Advanced MySQL Optimization: Using Hints to Precisely Control the Query Optimizer

What are MySQL Hints

MySQL Hints are special comments or directives that can be embedded directly in an SQL query to modify the optimizer’s default behavior. They are used when the optimizer chooses a sub‑optimal execution plan or when the developer knows the data distribution and query characteristics better than the optimizer.

Why use Hints

Performance tuning : In complex queries the optimizer may not select the optimal plan; a hint can force a more efficient strategy.

Control execution plan : Changes in data distribution or schema can cause the optimizer to switch plans; a hint keeps the plan stable.

Resolve specific problems : Issues such as poor index choice or join order can be addressed directly with a hint.

How to use Hints

Hints are added as a specially formatted comment placed immediately before the SQL statement. The syntax is /*+ HintName(parameters) */. The comment applies only to the following statement.

1. Identify the required hint

Analyze the query performance and determine which optimizer behavior needs to be altered. For example, if the optimizer does not use the desired index, FORCE INDEX or IGNORE INDEX may be appropriate.

2. Write the hint comment

Insert the comment with the hint name and any required arguments.

/*+ FORCE INDEX(table_name idx_name) */

table_name

is the target table and idx_name is the index to force.

3. Attach the hint to the SQL

Place the comment directly before the query without intervening characters or line breaks.

/*+ FORCE INDEX(my_table my_index) */ SELECT * FROM my_table WHERE my_column = 'value';

The FORCE INDEX hint forces the optimizer to use my_index on my_table.

4. Test and verify

Run EXPLAIN with the hint to confirm that the intended plan is used.

EXPLAIN /*+ FORCE INDEX(my_table my_index) */ SELECT * FROM my_table WHERE my_column = 'value';

Syntax note

The /*+ ... */ comment style originates from Oracle. MySQL also supports the native syntax without the comment, e.g.

SELECT * FROM my_table FORCE INDEX (my_index) WHERE my_column = 'value';

Common MySQL Hints

1. USE INDEX and FORCE INDEX

USE INDEX

is advisory; FORCE INDEX is mandatory.

-- USE INDEX example
SELECT * FROM users USE INDEX (idx_age) WHERE age > 30;

-- FORCE INDEX example
SELECT * FROM users FORCE INDEX (idx_age) WHERE age > 30;

2. IGNORE INDEX

SELECT * FROM users IGNORE INDEX (idx_age) WHERE name = 'John Doe';

3. STRAIGHT_JOIN

SELECT * FROM users STRAIGHT_JOIN orders ON users.id = orders.user_id;

4. SQL_NO_CACHE

SELECT SQL_NO_CACHE * FROM users WHERE age > 30;

5. INDEX_MERGE and NO_INDEX_MERGE

-- Encourage index merge
SELECT * FROM users INDEX_MERGE (idx_age, idx_name) WHERE age = 30 OR name = 'John Doe';

-- Prevent index merge
SELECT * FROM users NO_INDEX_MERGE WHERE age = 30 OR name = 'John Doe';

6. JOIN_FIXED_ORDER

SELECT * FROM table1 JOIN_FIXED_ORDER JOIN table2 ON table1.id = table2.table1_id;

7. BLOCK_NESTED_LOOP, BATCHED_KEY_ACCESS, NO_BNL, NO_BKA

-- BLOCK_NESTED_LOOP example
SELECT * FROM users a BLOCK_NESTED_LOOP JOIN orders b ON a.id = b.user_id;

-- BATCHED_KEY_ACCESS example
SELECT * FROM users a BATCHED_KEY_ACCESS JOIN orders b ON a.id = b.user_id;

-- NO_BNL example
SELECT * FROM users a NO_BNL JOIN orders b ON a.id = b.user_id;

-- NO_BKA example
SELECT * FROM users a NO_BKA JOIN orders b ON a.id = b.user_id;

8. MRR and NO_MRR

-- MRR example
SELECT * FROM users WHERE id IN (1,3,5) PROCEDURE ANALYSE() MRR;

-- NO_MRR example
SELECT * FROM users WHERE id IN (1,3,5) PROCEDURE ANALYSE() NO_MRR;

9. FILESORT and NO_FILESORT

-- Force file sort
SELECT * FROM users ORDER BY age FILESORT;

-- Prevent file sort
SELECT * FROM users ORDER BY age NO_FILESORT;

10. SUBQUERY and NO_SUBQUERY

-- Encourage subquery
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100) SUBQUERY;

-- Discourage subquery (may be transformed to JOIN)
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100) NO_SUBQUERY;

11. DERIVED_MERGE and NO_DERIVED_MERGE

-- Encourage derived table merge
SELECT * FROM (SELECT * FROM users WHERE age > 25) AS derived1 DERIVED_MERGE JOIN orders ON derived1.id = orders.user_id;

-- Prevent derived table merge
SELECT * FROM (SELECT * FROM users WHERE age > 25) AS derived1 NO_DERIVED_MERGE JOIN orders ON derived1.id = orders.user_id;

Precautions when using Hints

Use cautiously : Overuse or misuse can degrade performance because the hint overrides the optimizer’s decisions.

Test and verify : Benchmark queries before and after applying a hint to ensure the expected improvement.

Version compatibility : Not all MySQL versions support every hint; verify support for the target version.

Maintainability : Embedding hints can reduce readability; ensure team members understand the hint usage.

Reference: https://dev.mysql.com/doc/refman/8.0/en/controlling-optimizer.html