Hints

An optimizer hint, a /*+ ... */ comment after the first keyword, overrides one decision for one statement, at query-block (JOIN_ORDER, SEMIJOIN), table (MERGE, BNL) or index level (INDEX, NO_INDEX); SET_VAR sets a variable and MAX_EXECUTION_TIME caps a SELECT.

SELECT /*+ MAX_EXECUTION_TIME(200) */ COUNT(*) FROM order_items WHERE quantity * unit_price > 90;
SELECT /*+ INDEX(orders customer_id) */ COUNT(*) INTO @n FROM orders
WHERE customer_id BETWEEN 1000 AND 30000 AND status = 'paid';
SELECT /*+ NO_INDX(orders) */ COUNT(*) INTO @n FROM orders WHERE id < 10;
EXPLAIN SELECT /*+ NO_MERGE(dt) NO_DERIVED_CONDITION_PUSHDOWN(dt) */ * FROM
(SELECT id, customer_id FROM orders WHERE status = 'pending') AS dt WHERE dt.customer_id = 42\G

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
Query OK, 1 row affected (0.381 sec)
Query OK, 1 row affected, 1 warning (0.000 sec)
Warning (Code 1064): Optimizer hint syntax error near 'NO_INDX(orders) */ COUNT(*) INTO @n
  FROM orders WHERE id < 10' at line 1
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on dt using <auto_key0> (customer_id = 42)  (cost=61699..61702
  rows=10)
    -> Materialize  (cost=61698..61698 rows=49891)
        -> Filter: (orders.`status` = 'pending')  (cost=50203 rows=49891)
            -> Table scan on orders  (cost=50203 rows=499223)
1 row in set (0.000 sec)

Forcing customer_id onto the wide range of Cost-Based Optimizer took 0.38 s against 0.10 s for the optimizer's scan, and a misspelled hint is only a warning. The last plan shows what derived-table merging saves: unhinted, the subquery merges into the outer query and becomes an index lookup (0.06 ms), and under NO_MERGE alone, condition pushdown still copies the filter inside. With both off, all pending orders are materialized and indexed (<auto_key0>) first: 167 ms. The older index hints after a table name (USE, IGNORE, FORCE INDEX) are superseded by index-level hints, and the manual says to expect their deprecation. Comment why every hint exists.