MySQL Query Optimiser Hints: INDEX_MERGE, NO_RANGE_OPTIMIZATION, and SET_VAR

Home » MySQL » MySQL Query Optimiser Hints: INDEX_MERGE, NO_RANGE_OPTIMIZATION, and SET_VAR
March 20, 2026 MySQL
MySQL Query Optimiser Hints: INDEX_MERGE, NO_RANGE_OPTIMIZATION, and SET_VAR

MySQL’s optimiser hints (available since 5.7, extended in 8.0) let you override the query planner’s decisions without changing session variables or schema — they are embedded directly in the query with /*+ HINT */ syntax and affect only that specific statement. For WordPress performance work, hints are invaluable when the optimiser consistently makes a bad plan choice that cannot be fixed with index changes alone.

Problem: The MySQL query optimiser makes poor index choices on complex WordPress queries — choosing a full table scan over an available index, or merging indexes in a suboptimal way — and there is no obvious way to override the optimiser without changing the query.

Solution: Use MySQL optimiser hints directly in the SQL query: SELECT /*+ INDEX(wp_posts idx_post_date) */ ... forces index selection, /*+ NO_RANGE_OPTIMIZATION(...) */ disables range scans on a specific index, and /*+ SEMIJOIN() */ controls subquery strategy. Hints are ignored gracefully on older MySQL versions. Use EXPLAIN FORMAT=TREE to verify the chosen plan.


The examples below demonstrate index selection hints on wp_posts, disabling index merge for a query that performs better with a single index, the SET_VAR hint for statement-level variable overrides, and the JOIN_ORDER hint for complex WooCommerce meta joins.


-- ── 1. Force a specific index ─────────────────────────────────────────────
-- Without hint: optimiser may choose a suboptimal index on wp_posts
SELECT /*+ INDEX(p type_status_date) */ ID, post_title
FROM   wp_posts p
WHERE  post_type   = 'product'
  AND  post_status = 'publish'
ORDER  BY post_date DESC
LIMIT  20;

-- ── 2. Ignore a specific index (prevent bad index choice) ────────────────
SELECT /*+ NO_INDEX(pm PRIMARY) */ p.ID, p.post_title
FROM   wp_posts p
  JOIN wp_postmeta pm ON pm.post_id = p.ID
WHERE  pm.meta_key   = '_price'
  AND  pm.meta_value > '100'
  AND  p.post_status = 'publish';

-- ── 3. Disable index merge (when ALL reads perform better than bitwise AND)
SELECT /*+ NO_INDEX_MERGE(p) */ ID
FROM   wp_posts p
WHERE  post_type = 'product' OR post_status = 'draft';

-- ── 4. SET_VAR hint: override a session variable for one query only ───────
-- Increase sort buffer for a large ORDER BY without changing the session
SELECT /*+ SET_VAR(sort_buffer_size = 4194304) */
    p.ID,
    p.post_title,
    MAX( CAST(pm.meta_value AS DECIMAL(10,2)) ) AS price
FROM   wp_posts p
  JOIN wp_postmeta pm ON pm.post_id = p.ID AND pm.meta_key = '_price'
WHERE  p.post_type = 'product'
GROUP  BY p.ID
ORDER  BY price DESC
LIMIT  100;

-- ── 5. JOIN_ORDER hint: force a specific join order ───────────────────────
-- When WooCommerce joins order_items → order_itemmeta → posts in bad order
SELECT /*+ JOIN_ORDER(oi, oim, p) */
    oi.order_id,
    oim.meta_value  AS product_qty,
    p.post_title    AS product_name
FROM   wp_woocommerce_order_items oi
  JOIN wp_woocommerce_order_itemmeta oim ON oim.order_item_id = oi.order_item_id
  JOIN wp_posts p ON p.ID = oim.meta_value
WHERE  oi.order_item_type  = 'line_item'
  AND  oim.meta_key        = '_product_id';

-- ── 6. Verify the hint was accepted ───────────────────────────────────────
EXPLAIN FORMAT=JSON
SELECT /*+ INDEX(p type_status_date) */ ID FROM wp_posts p
WHERE post_type='post' AND post_status='publish';
-- Look for "index_name": "type_status_date" in the JSON output


NOTE: Optimiser hints bypass the query planner's statistics — if your table data changes significantly (e.g., a bulk import doubles the row count), a hardcoded hint may force a plan that was optimal before the import but is now suboptimal; audit hinted queries after major data changes with EXPLAIN ANALYZE to verify the forced plan is still the best choice.

Related posts