QUERY HINTS / OPTIMIZER HINTSQuery Techniques
Buy Me A Coffee

Directives embedded in a SQL statement that instruct the query optimizer to use a specific join order, index, or execution strategy.

Directives embedded in comments that suggest a specific execution plan to the optimizer (e.g., FORCE ORDER, USE INDEX). Useful when the planner makes poor choices, but considered a code smell -- better to fix statistics or indexes. MySQL, SQL Server, and Oracle support hints.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.0 Index hints: USE INDEX, FORCE INDEX, IGNORE INDEX in the FROM clause. Optimizer hints (/*+ ... */ comment syntax) added in 8.0, including JOIN_ORDER, NO_MERGE, MAX_EXECUTION_TIME, and more.
PostgreSQL ✓ Supported 7.4 No dedicated hint syntax. Hints are applied indirectly via session-level planner configuration: enable_indexscan, enable_seqscan, enable_hashjoin, etc. The pg_hint_plan extension provides Oracle/SQL Server-style inline hints and is widely used in production.
SQL Server ✓ Supported 2000 Rich hint vocabulary: table hints (WITH (NOLOCK), WITH (INDEX(...))), join hints (LOOP, HASH, MERGE in FROM clause), and query hints (OPTION clause: OPTION (RECOMPILE), OPTION (MAXDOP 1), OPTION (HASH JOIN)). NOLOCK is commonly overused — it produces dirty reads.
Oracle ✓ Supported 7 Hints use the /*+ ... */ comment syntax. Extensive vocabulary including INDEX, NO_INDEX, FULL, LEADING, USE_NL, USE_HASH, PARALLEL, APPEND, and many more. Oracle's hint system is the most mature and granular among these engines.
SQLite ✗ Not Supported — No query hint syntax. The INDEXED BY clause forces use of a named index but is not a general-purpose hint system. Use EXPLAIN QUERY PLAN to inspect the chosen strategy.

Details

Query hints are a last resort — the right response to a bad query plan is usually better statistics, updated indexes, or a query rewrite. That said, hints are invaluable when a known-good plan is being abandoned due to stale stats or parameter sniffing. Oracle and SQL Server have the most mature hint systems with the widest vocabulary. PostgreSQL deliberately omits inline hints to keep the optimizer honest, but the pg_hint_plan extension fills the gap for production use. MySQL's FORCE INDEX is deceptively aggressive — it tells the optimizer to use that index or do a full table scan, with no middle ground. SQL Server's WITH (NOLOCK) is the most commonly misused hint: it eliminates lock waits at the cost of dirty reads, phantom reads, and even rows appearing twice or not at all during a scan — not appropriate for financial or transactional queries.

Standard Syntax

-- SQL Server SELECT * FROM orders WITH (INDEX(idx_customer)) WHERE customer_id = 42; -- MySQL SELECT * FROM orders USE INDEX (idx_customer) WHERE customer_id = 42;

Version Support

MySQL: Since 5.0 PostgreSQL: Since 7.4 SQL Server: Since 2000 Oracle: Since 7 SQLite: Not supported

Per-Database Syntax & Notes

MySQL

Index hints: USE INDEX, FORCE INDEX, IGNORE INDEX in the FROM clause. Optimizer hints (/*+ ... */ comment syntax) added in 8.0, including JOIN_ORDER, NO_MERGE, MAX_EXECUTION_TIME, and more.

-- Index hint: SELECT * FROM orders USE INDEX (idx_customer) WHERE customer_id = 42; SELECT * FROM orders FORCE INDEX (idx_customer) WHERE customer_id = 42; -- Optimizer hint (8.0+): SELECT /*+ NO_INDEX_MERGE(orders) */ * FROM orders WHERE customer_id = 42;

PostgreSQL

No dedicated hint syntax. Hints are applied indirectly via session-level planner configuration: enable_indexscan, enable_seqscan, enable_hashjoin, etc. The pg_hint_plan extension provides Oracle/SQL Server-style inline hints and is widely used in production.

-- Session-level planner toggle: SET enable_seqscan = off; SELECT * FROM orders WHERE customer_id = 42; SET enable_seqscan = on; -- With pg_hint_plan extension: SELECT /*+ IndexScan(orders idx_customer) */ * FROM orders WHERE customer_id = 42;

SQL Server

Rich hint vocabulary: table hints (WITH (NOLOCK), WITH (INDEX(...))), join hints (LOOP, HASH, MERGE in FROM clause), and query hints (OPTION clause: OPTION (RECOMPILE), OPTION (MAXDOP 1), OPTION (HASH JOIN)). NOLOCK is commonly overused — it produces dirty reads.

-- Index hint: SELECT * FROM orders WITH (INDEX(idx_customer)) WHERE customer_id = 42; -- Query hints: SELECT * FROM orders WHERE customer_id = 42 OPTION (RECOMPILE); SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id OPTION (HASH JOIN, MAXDOP 4);

Oracle

Hints use the /*+ ... */ comment syntax. Extensive vocabulary including INDEX, NO_INDEX, FULL, LEADING, USE_NL, USE_HASH, PARALLEL, APPEND, and many more. Oracle's hint system is the most mature and granular among these engines.

SELECT /*+ INDEX(o idx_customer) */ * FROM orders o WHERE customer_id = 42; SELECT /*+ LEADING(c o) USE_NL(o) */ o.* FROM customers c JOIN orders o ON c.id = o.customer_id WHERE c.tier = 'gold';

SQLite

No query hint syntax. The INDEXED BY clause forces use of a named index but is not a general-purpose hint system. Use EXPLAIN QUERY PLAN to inspect the chosen strategy.

-- INDEXED BY (forces specific index, not a general hint): SELECT * FROM orders INDEXED BY idx_customer WHERE customer_id = 42; -- Inspect plan: EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;