MySQL Optimizer Hints: INDEX, JOIN_ORDER, and MAX_EXECUTION_TIME

-- Hints go in a comment immediately after SELECT/INSERT/UPDATE/DELETE
SELECT /*+ hint_name(args) */ columns FROM table;

-- Force a specific index
SELECT /*+ INDEX(orders idx_orders_created_at) */
  id, amount FROM orders WHERE created_at > '2025-01-01';

-- Force no index (full table scan)
SELECT /*+ NO_INDEX(orders idx_orders_status) */
  * FROM orders WHERE status = 'active';

-- Force index for ORDER BY (avoid filesort)
SELECT /*+ INDEX_ORDER(orders idx_orders_created_at) */
  * FROM orders ORDER BY created_at DESC LIMIT 100;

-- Force join order (drive from orders, then join users)
SELECT /*+ JOIN_ORDER(orders, users) */
  o.id, u.email
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';

-- Force nested loop join
SELECT /*+ JOIN_FIXED_ORDER() BNL(orders, users) */
  o.id, u.email
FROM orders o JOIN users u ON o.user_id = u.id;

-- Run query in a resource group (MySQL 8.0+)
SELECT /*+ RESOURCE_GROUP(analytics_group) */
  region, sum(amount) FROM orders GROUP BY region;

-- Kill query if it takes more than 5 seconds
SELECT /*+ MAX_EXECUTION_TIME(5000) */
  * FROM large_table WHERE complex_condition;

-- Name subquery blocks to apply hints to them
SELECT /*+ QB_NAME(outer) */
  id FROM orders
WHERE user_id IN (
  SELECT /*+ QB_NAME(inner) INDEX(users idx_users_status) */ id
  FROM users WHERE status = 'premium'
);