Skip to content
Tutorials Center
My learning

Topics

Lesson 47 of 75 ยท Indexes and Performance

Optimizer Hints, Index Hints and optimizer_trace

Steer the MySQL optimizer: USE, FORCE and IGNORE INDEX, /*+ */ hints, invisible indexes, optimizer_switch and optimizer_trace explained.

MySQL's optimizer chooses a plan for every query: which index to use, in which order to join tables, whether to sort or to read an index that is already in order. It decides with cost estimates built from table statistics, and most of the time it is right. Occasionally it is not. A stale statistic, a skewed column or an odd mix of WHERE and ORDER BY can make it pick a plan that is ten times slower than an obvious alternative.

This lesson is about what to do then. You will learn the three layers of control MySQL gives you, from the oldest to the newest, and, more importantly, how to find out why the optimizer chose what it did, using optimizer_trace. The query-tuning lesson already introduced hints in a few lines; here we go through them properly.

MySQL's hint syntax is MySQL-only, so those statements are read-only here. SQLite has an equivalent feature, INDEXED BY and NOT INDEXED, and the runnable examples use it to show the same idea live.

Fix the cause before the symptom

Hints are the last tool, not the first. Before forcing anything, check in this order:

  1. Statistics. Run ANALYZE TABLE orders; so the optimizer sees current row counts and key distributions. For heavily skewed columns, MySQL 8.0 can also keep a histogram: ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;.
  2. The index itself. Is there an index that fits the WHERE and ORDER BY? The composite index lesson covers that design.
  3. The query. A function on the column or a type mismatch makes an index unusable, and no hint can fix that. See sargable predicates.

If all three are in order and the plan is still wrong, reach for a hint.

Layer 1: index hints

Index hints attach to a table in the FROM clause. There are three:

SQL
SELECT * FROM orders USE INDEX (idx_customer_placed)    WHERE customer_id = 7;
SELECT * FROM orders FORCE INDEX (idx_customer_placed)  WHERE customer_id = 7;
SELECT * FROM orders IGNORE INDEX (idx_status)          WHERE status = 'paid';
Hint Meaning
USE INDEX (i) "Consider only these indexes." The optimizer may still prefer a table scan if it thinks that is cheaper.
FORCE INDEX (i) Like USE, but a table scan is treated as extremely expensive, so the index is used if it can be at all.
IGNORE INDEX (i) "Never use this index." Everything else stays open.

You can narrow a hint to one job with FOR JOIN, FOR ORDER BY or FOR GROUP BY, for instance IGNORE INDEX FOR ORDER BY (idx_a). The special name PRIMARY refers to the primary key. In practice, IGNORE INDEX is the safest of the three because it only removes a bad option, while FORCE INDEX locks in a single choice.

Index hints, live in SQLite

SQLite's INDEXED BY idx behaves like FORCE INDEX, and NOT INDEXED means "no index at all". With EXPLAIN QUERY PLAN you can see the effect immediately:

SQL Steering the plan with INDEXED BY

Press Run (or Ctrl + Enter) to see the result here.

The first plan searches idx_customer, which matches about 60 rows. The second is forced to use idx_status, which matches 2,700 rows and then filters, and the third scans the table. All three return the same rows; only the work differs. That is exactly the risk of hints: they change speed, never meaning, and they remove the optimizer's chance to adapt.

Layer 2: optimizer hints

MySQL 5.7 introduced a newer syntax, the optimizer hint comment, written right after the statement keyword. Because it is a /*+ ... */ comment, other databases ignore it, and old servers do too.

SQL
SELECT /*+ NO_INDEX(o idx_status) */ o.id
FROM orders AS o
WHERE o.status = 'paid' AND o.customer_id = 7;

SELECT /*+ JOIN_ORDER(c, o) MAX_EXECUTION_TIME(2000) */
       c.name, SUM(o.total)
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.name;

SELECT /*+ SET_VAR(sort_buffer_size = 4M) */ * FROM orders ORDER BY placed_at;

Compared to index hints, optimizer hints are more precise because they can name a table (o) or a query block, and more varied:

  • Index level: INDEX, NO_INDEX, JOIN_INDEX, GROUP_INDEX, ORDER_INDEX (and their NO_ forms).
  • Table level: BKA, NO_BKA, BNL, NO_BNL, HASH_JOIN, NO_HASH_JOIN, DERIVED_CONDITION_PUSHDOWN.
  • Join order: JOIN_ORDER, JOIN_PREFIX, JOIN_SUFFIX.
  • Subqueries: SEMIJOIN, NO_SEMIJOIN, SUBQUERY.
  • Merging views and derived tables: MERGE, NO_MERGE.
  • Statement level: MAX_EXECUTION_TIME(ms) (for SELECT only), SET_VAR(var = value) and RESOURCE_GROUP.

A hint can only apply to a table it names, and QB_NAME(q1) gives a subquery a label so hints in the outer query can reach it. If a hint is ignored because of a typo or a nonexistent index, MySQL does not fail: it records a warning. After a hinted statement, run SHOW WARNINGS; and look for "Unresolved name" notes.

MAX_EXECUTION_TIME deserves special mention because it is not about plans at all. It cancels a read-only query that runs longer than the limit, which makes it a cheap safety net for ad hoc reports.

Layer 3: the switchboard, optimizer_switch

Hints work on one statement. To turn an optimizer feature on or off for a whole session, MySQL has the optimizer_switch variable, a set of flags:

SQL
SHOW VARIABLES LIKE 'optimizer_switch'\G
SET SESSION optimizer_switch = 'index_merge=off,block_nested_loop=off';
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

Flags include index_merge, index_condition_pushdown, semijoin, derived_merge, hash_join and skip_scan. This is mostly a diagnostic tool: switch a feature off in your session, re-run EXPLAIN, and see whether the plan that comes back is faster. If it is, use the matching /*+ NO_... */ hint for the one query instead of changing global behavior.

Invisible indexes: testing without risk

An invisible index is maintained like any other but ignored by the optimizer:

SQL
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
-- test, compare EXPLAIN and timing, then either:
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;
-- or drop it for good

They work as a reversible IGNORE INDEX for the whole server, which is ideal before dropping an index you suspect is unused. You can try the opposite for one session with use_invisible_indexes=on and check whether a hidden index would help a query.

optimizer_trace: ask the optimizer why

EXPLAIN tells you what the plan is. optimizer_trace tells you why: which access paths were considered, their estimated costs, and which were rejected. It records a JSON document for the statements you run while it is on.

SQL
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1048576;   -- raise if the trace is cut off

SELECT * FROM orders WHERE customer_id = 7 AND status = 'paid';

SELECT QUERY, TRACE FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace = 'enabled=off';

The TRACE column is large JSON. You rarely read it top to bottom. The useful parts are:

Section What you learn
considered_execution_plans The join orders tried, each with cost and rows_for_plan.
range_analysis > analyzing_range_alternatives Each index considered for a range scan, with its estimated rows and cost, and a cause such as cost when it lost.
rows_estimation How many rows each table is expected to return and by which method (index_dives, histogram, statistics).
attaching_conditions_to_tables Which part of the WHERE is checked at which table.
refine_plan The final plan and any index condition pushdown.

A typical rejected alternative looks like this: the optimizer lists idx_status with a cost of about 2,900 and idx_customer with about 70, then marks the first as "chosen": false, "cause": "cost". Seeing that, you know the choice was a cost decision, and you can ask whether the estimate was wrong (stale statistics, a histogram needed) rather than reaching for FORCE INDEX immediately.

Practical tips:

  • The trace is per session and holds only the last few statements (optimizer_trace_limit, default 1). Enable it, run one query, read it, disable it.
  • Tracing has overhead and needs the INFORMATION_SCHEMA read privilege. Use it on a replica or a test copy.
  • EXPLAIN FORMAT=JSON shows the chosen plan's costs; optimizer_trace also shows the rejected plans.
  • The format changes between releases, so don't build tooling that parses it.

A decision path

Wrong plan? Work down the list

Refresh statistics ANALYZE TABLE, histograms.
Fix the index Match WHERE and ORDER BY.
Fix the query Sargable, same types.
Read the trace Find why a plan lost.

Hints come after all four.

1 / 2

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the difference between USE INDEX and FORCE INDEX?

    USE INDEX limits the candidate list but leaves the final cost decision to the optimizer. FORCE INDEX effectively overrides it.
  2. 2. Which syntax is an optimizer hint, rather than an index hint?

    Optimizer hints are written as /*+ ... */ comments after the SELECT keyword. USE, FORCE and IGNORE INDEX are index hints in the FROM clause.
  3. 3. What does an invisible index do?

    An invisible index still gets updated on every write but the optimizer will not choose it, which makes dropping safe to test.
  4. 4. What does optimizer_trace give you that EXPLAIN does not?

    EXPLAIN shows the chosen plan. The trace records alternatives the optimizer considered and why they lost.
  5. 5. A statement has a hint naming an index that does not exist. What happens?

    Optimizer hints that cannot be applied are ignored; check SHOW WARNINGS for the unresolved name.

Summary

  • Fix statistics, indexes and the query before reaching for hints.
  • USE, FORCE and IGNORE INDEX are the old index hints; /*+ ... */ optimizer hints are newer, can target a table or query block and cover joins, subqueries, timeouts and SET_VAR.
  • optimizer_switch toggles optimizer features per session, mainly for diagnosis.
  • Invisible indexes let you test the effect of removing an index and undo it instantly.
  • optimizer_trace records the considered plans, costs and reasons, so you can see whether the estimate or the plan was the problem.
  • Every hint freezes a decision made on today's data; document it and review it after upgrades.

Next: The Slow Query Log and Profiling, which finds the queries worth tuning in the first place.