Index Usage - Learning Module

Loading content...

0/241

Index Hints

Taking Control of Index Selection

The query optimizer is remarkably sophisticated, but it's not omniscient. It works with statistical estimates that may be outdated, inaccurate, or unable to capture complex data correlations. In these situations, index hints provide a mechanism to guide—or override—the optimizer's decisions.

Index hints are like giving directions to a GPS navigation system. Most of the time, you trust the system's routing. But when you know about construction, traffic patterns, or shortcuts that the system doesn't, you take manual control. Similarly, when you understand your data better than the optimizer's statistics reflect, hints let you share that knowledge.

Use Hints Sparingly

Index hints should be a last resort, not a first choice. They create maintenance burdens, can become counterproductive as data changes, and may mask underlying issues that should be fixed properly (like stale statistics or missing indexes). Always try to solve the problem through better statistics, index design, or query restructuring before resorting to hints.

What You Will Learn

By the end of this page, you will understand the different types of index hints available across major database systems, when and why to use them, the risks and maintenance implications, and best practices for hint usage in production systems.

When Index Hints Are Justified

Before diving into hint syntax, let's establish the legitimate use cases. Hints should only be considered when:

1. Statistics Cannot Capture Reality

Some data patterns are inherently difficult for statistics to model:

Highly correlated columns: When column A value largely determines column B value
Skewed distributions with parameters: When filter values in the query are always in the rare tail
Temporal patterns: When recent data has different characteristics than historical data

2. Optimizer Makes Consistently Wrong Choices

If the same query repeatedly gets a bad plan despite fresh statistics and proper indexing, and you've verified via EXPLAIN that a different plan is dramatically better.

3. Critical Performance Paths

For queries that must have guaranteed, predictable performance—like those in tight SLAs or real-time systems—hints can prevent plan regressions.

4. Testing and Benchmarking

When comparing index strategies or diagnosing performance issues, hints let you force specific access paths for controlled experiments.

When NOT to Use Hints

•As a substitute for proper indexing — If the right index doesn't exist, create it instead of forcing a suboptimal one
•To work around stale statistics — Refresh statistics instead; this fixes all queries, not just one
•When you don't understand why the optimizer chose differently — Investigate first; the optimizer might be right
•For all queries by default — Hints should be surgical, not a blanket approach
•In application code without documentation — Future developers need to understand why

The 10x Rule

Only use a hint if it provides at least a 10x improvement AND you've verified it across representative data samples. Smaller improvements usually aren't worth the maintenance burden and risk of hints becoming stale as data evolves.

MySQL Index Hints

MySQL provides several hint mechanisms with different levels of force:

USE INDEX: Suggests which indexes to consider (optimizer may still reject) FORCE INDEX: Strongly suggests index usage (optimizer heavily prefers these) IGNORE INDEX: Excludes specific indexes from consideration

mysql-index-hints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Basic USE INDEX syntax
SELECT * FROM orders USE INDEX (idx_customer_id)
WHERE customer_id = 12345 AND status = 'pending';
 
-- Multiple indexes can be suggested
SELECT * FROM orders USE INDEX (idx_customer_id, idx_status)
WHERE customer_id = 12345 AND status = 'pending';
 
-- FORCE INDEX - stronger preference
SELECT * FROM orders FORCE INDEX (idx_customer_id)
WHERE customer_id = 12345;
 
-- IGNORE INDEX - exclude specific indexes
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE customer_id = 12345 AND status = 'pending';
 
-- Context-specific hints (MySQL 5.1+)
-- Specify which operation the hint applies to
SELECT * FROM orders 
USE INDEX FOR JOIN (idx_customer_id)
USE INDEX FOR ORDER BY (idx_order_date)
WHERE customer_id = 12345
ORDER BY order_date;
 
-- Force full table scan by ignoring all indexes
SELECT * FROM orders USE INDEX ()
WHERE customer_id = 12345;

MySQL 8.0+ Optimizer Hints

MySQL 8.0 introduced a more comprehensive hint syntax using comments:

mysql-optimizer-hints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Optimizer hint syntax (MySQL 8.0+)
SELECT /*+ INDEX(orders idx_customer_id) */ *
FROM orders
WHERE customer_id = 12345;
 
-- No index (force full scan)
SELECT /*+ NO_INDEX(orders idx_customer_id) */ *
FROM orders  
WHERE customer_id = 12345;
 
-- Index merge hint
SELECT /*+ INDEX_MERGE(orders idx_customer_id, idx_status) */ *
FROM orders
WHERE customer_id = 12345 OR status = 'pending';
 
-- Skip scan hint (for leading column not in WHERE)
SELECT /*+ SKIP_SCAN(orders idx_composite) */ *
FROM orders
WHERE status = 'pending';  -- status is not first column in idx_composite
 
-- Join order hints (related but useful with indexes)
SELECT /*+ JOIN_ORDER(customers, orders) */ c.name, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;
 
-- Combining multiple hints
SELECT /*+ INDEX(orders idx_customer_id) BNL(orders) */ *
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'USA';

USE vs FORCE in MySQL

USE INDEX is a 'soft' hint—the optimizer considers the indexes but may still choose a table scan if the cost estimate favors it. FORCE INDEX makes a table scan extremely expensive in cost calculations, essentially guaranteeing index usage as long as the index can be used for the query. Use FORCE only when USE doesn't achieve the desired effect.

PostgreSQL Approach to Index Guidance

PostgreSQL takes a different philosophical approach: it doesn't have traditional index hints. The PostgreSQL developers believe that if the optimizer makes a wrong choice, the solution is to fix the underlying cause (statistics, configuration, or index design) rather than work around it with hints.

However, PostgreSQL provides configuration parameters that influence index selection for testing or emergency purposes:

postgresql-index-control.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Disable specific scan types to force index usage
SET enable_seqscan = OFF;      -- Disable sequential scans
SET enable_indexscan = ON;     -- Ensure index scans are considered
SET enable_bitmapscan = ON;    -- Ensure bitmap scans are considered
 
-- Now run your query - optimizer will avoid seq scans if any alternative exists
SELECT * FROM orders WHERE customer_id = 12345;
 
-- Re-enable for normal operation
SET enable_seqscan = ON;
 
-- These are session-level settings; can also be transaction-scoped
BEGIN;
SET LOCAL enable_seqscan = OFF;
SELECT * FROM orders WHERE customer_id = 12345;
COMMIT;  -- Setting reverts after commit
 
-- Adjust cost parameters to influence optimizer
SET random_page_cost = 1.1;    -- Make random I/O cheaper (SSD)
SET seq_page_cost = 1.0;       -- Sequential I/O cost
SET cpu_tuple_cost = 0.01;     -- Cost per row processed
SET cpu_index_tuple_cost = 0.005;  -- Cost per index entry
 
-- These parameters affect ALL queries in the session
-- Lowering random_page_cost favors index scans on SSDs

The pg_hint_plan Extension

For situations requiring direct index hints, the popular pg_hint_plan extension provides MySQL-style hinting:

pg-hint-plan.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Install pg_hint_plan extension
CREATE EXTENSION pg_hint_plan;
 
-- Index scan hints
/*+ IndexScan(orders idx_customer_id) */
SELECT * FROM orders WHERE customer_id = 12345;
 
-- Index only scan (covering index)
/*+ IndexOnlyScan(orders idx_covering) */
SELECT customer_id, order_date FROM orders 
WHERE customer_id = 12345;
 
-- Bitmap scan hint
/*+ BitmapScan(orders idx_customer_id) */
SELECT * FROM orders WHERE customer_id = 12345;
 
-- Force sequential scan (useful for testing)
/*+ SeqScan(orders) */
SELECT * FROM orders WHERE customer_id = 12345;
 
-- No index at all
/*+ NoIndexScan(orders) */
SELECT * FROM orders WHERE customer_id = 12345;
 
-- Combine with join hints
/*+ IndexScan(o idx_customer_id) NestLoop(c o) Leading(c o) */
SELECT c.name, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'USA';

PostgreSQL Philosophy

PostgreSQL's lack of built-in hints is intentional. The philosophy is that hints are a maintenance liability—they freeze decisions that should adapt as data changes. If you find yourself needing hints frequently, it may indicate a need to review your statistics collection frequency, index design, or PostgreSQL configuration for your workload characteristics.

SQL Server and Oracle Index Hints

SQL Server Index Hints

SQL Server uses a WITH clause for table hints, including index specifications:

sqlserver-index-hints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Force specific index
SELECT * FROM orders WITH (INDEX(idx_customer_id))
WHERE customer_id = 12345;
 
-- Force index by number (fragile - index numbers can change)
SELECT * FROM orders WITH (INDEX(2))
WHERE customer_id = 12345;
 
-- Force table scan (ignore all indexes)
SELECT * FROM orders WITH (INDEX(0))
WHERE customer_id = 12345;
 
-- Force clustered index scan
SELECT * FROM orders WITH (INDEX(1))   -- Index 1 is always clustered
WHERE customer_id = 12345;
 
-- Multiple hints combined
SELECT * FROM orders WITH (INDEX(idx_customer_id), NOLOCK)
WHERE customer_id = 12345;
 
-- FORCESEEK - force an index seek instead of scan
SELECT * FROM orders WITH (FORCESEEK)
WHERE customer_id = 12345;
 
-- FORCESCAN - force an index or table scan
SELECT * FROM orders WITH (FORCESCAN)
WHERE customer_id = 12345;
 
-- Query hint for entire statement
SELECT * FROM orders
WHERE customer_id = 12345
OPTION (TABLE HINT(orders, INDEX(idx_customer_id)));

Oracle Index Hints

Oracle uses comment-style hints, one of the most comprehensive hint systems:

oracle-index-hints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Basic index hint
SELECT /*+ INDEX(orders idx_customer_id) */ *
FROM orders
WHERE customer_id = 12345;
 
-- Index with table alias
SELECT /*+ INDEX(o idx_customer_id) */ *
FROM orders o
WHERE o.customer_id = 12345;
 
-- Force full table scan
SELECT /*+ FULL(orders) */ *
FROM orders
WHERE customer_id = 12345;
 
-- Index fast full scan (for covered queries)
SELECT /*+ INDEX_FFS(orders idx_covering) */ customer_id, COUNT(*)
FROM orders
GROUP BY customer_id;
 
-- Index range scan with specific start
SELECT /*+ INDEX_RS_ASC(orders idx_order_date) */ *
FROM orders
WHERE order_date > SYSDATE - 30;
 
-- Descending index scan
SELECT /*+ INDEX_RS_DESC(orders idx_order_date) */ *
FROM orders
WHERE order_date < SYSDATE
ORDER BY order_date DESC;
 
-- Index skip scan (when leading column not in predicate)
SELECT /*+ INDEX_SS(orders idx_composite) */ *
FROM orders
WHERE status = 'pending';  -- status is second column
 
-- No index - force different approach
SELECT /*+ NO_INDEX(orders idx_customer_id) */ *
FROM orders
WHERE customer_id = 12345;
 
-- Combine index with join hints
SELECT /*+ USE_NL(o) INDEX(o idx_customer_id) */ c.name, o.order_id
FROM customers c, orders o
WHERE c.customer_id = o.customer_id
  AND c.country = 'USA';

Hint Syntax Validation

Both SQL Server and Oracle silently ignore invalid hints (wrong syntax, non-existent index names). This can be problematic—a typo in an index name means the hint does nothing, but no error is raised. Always verify with EXPLAIN/execution plan that your hint is actually being applied.

Index Hint Categories and Strategies

Index hints fall into several categories, each serving different purposes:

1. Index Selection Hints

Force or prevent the use of specific indexes:

Index Selection Hint Types
Hint Type	Purpose	When to Use
Force specific index	Use exactly this index	When optimizer consistently picks wrong index
Exclude index	Don't use this index	When an index causes worse plans
Force table scan	Ignore all indexes	When full scan is faster for wide result sets
Suggest indexes	Prefer these indexes	When you want guidance without forcing

2. Access Method Hints

Control HOW an index is accessed:

Access Method Hint Types
Method	Description	Typical Use Case
Index Seek	Navigate to specific key values	Point lookups, range starts
Index Scan	Read entire index or large portion	When most index entries qualify
Index Skip Scan	Skip leading column values	Composite index without leading column filter
Bitmap Scan	Build bitmap from multiple indexes	OR conditions, multiple filter columns
Index Fast Full Scan	Read index blocks in physical order	Covered queries needing all index entries

access-method-hints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Oracle: Different index access methods
-- Index range scan (default for ranges)
SELECT /*+ INDEX_RS_ASC(o idx_date) */ * 
FROM orders o WHERE order_date > '2024-01-01';
 
-- Index skip scan (skip leading column)
SELECT /*+ INDEX_SS(products idx_cat_brand) */ *
FROM products 
WHERE brand_id = 100;  -- brand_id is second in composite index
 
-- Index fast full scan (scan index like a table)
-- Good for: SELECT columns that are in the index
SELECT /*+ INDEX_FFS(products idx_sku) */ COUNT(*) 
FROM products;
 
-- SQL Server: Seek vs Scan
SELECT * FROM orders WITH (FORCESEEK, INDEX(idx_customer));
-- Forces seek operation - fails if seek not possible
 
SELECT * FROM orders WITH (FORCESCAN, INDEX(idx_customer));  
-- Forces scan - may be useful for range covering most of index

Risks and Maintenance Considerations

Index hints come with significant risks that must be carefully managed:

1. Hints Can Become Counterproductive

Data distributions change over time. A hint that improved performance for today's data may hurt performance next year:

hint-degradation.sql
1
2
3
4
5
6
7
8
9
10
-- January 2024: 'pending' status is 2% of orders
-- Hint forces index, works great!
SELECT /*+ INDEX(orders idx_status) */ *
FROM orders WHERE status = 'pending';
 
-- December 2024: 'pending' is now 40% of orders (holiday backlog)
-- Same hint now forces expensive index scan 
-- Full table scan would be 3x faster!
 
-- The hint that helped now hurts, but application code unchanged

2. Index Changes Break Hints

If an index is renamed, dropped, or recreated with different columns, hints referencing it may:

Fail silently (Oracle, MySQL)
Cause errors (some SQL Server scenarios)
Cause plan regressions when hint no longer applies

3. Hints Hide Root Causes

Hints are symptomatic treatment. They may mask:

Stale statistics needing refresh
Poor index design
Query restructuring opportunities
Database configuration issues

4. Version Upgrade Risks

Database upgrades often improve optimizer intelligence. Hints that were necessary in version N may be counterproductive in version N+1 if the optimizer now handles the situation correctly.

Hint Maintenance Best Practices

•Document every hint: Include why it was added, when, what problem it solved, and expected behavior
•Add review dates: Schedule periodic review to verify hints are still beneficial
•Monitor hint effectiveness: Track query performance for hinted queries over time
•Test without hints: Periodically verify the hint is still needed—optimizer may have improved
•Include in code review: Hints should require justification and approval
•Centralize hint usage: Know where all hints are in your codebase

Technical Debt

Every hint is technical debt. It's a manual override of an automated system, requiring ongoing human attention to remain valuable. A codebase with many hints is a maintenance burden that scales poorly. Treat hints as temporary fixes requiring eventual elimination through proper optimization.

Real-World Hint Scenarios

Let's examine practical scenarios where hints might be justified:

Scenario 1: Parameter Sniffing Issues

The optimizer caches a plan based on the first parameter value, which may be atypical:

parameter-sniffing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Problem: First execution with rare status 'cancelled' (0.1%)
-- Optimizer creates plan using index scan
-- Later executions with 'delivered' (60%) use same cached plan
-- Index scan on 60% of table is terrible!
 
-- Solution 1 (SQL Server): Force recompile
SELECT * FROM orders WHERE status = @status
OPTION (RECOMPILE);
 
-- Solution 2: Use hint for known common case
SELECT * FROM orders WITH (INDEX(idx_status))
WHERE status = @status;
 
-- Solution 3 (SQL Server): OPTIMIZE FOR hint
SELECT * FROM orders WHERE status = @status
OPTION (OPTIMIZE FOR (@status = 'delivered'));

Scenario 2: Covering Index Not Chosen

The optimizer doesn't realize an index covers all needed columns:

covering-index-hint.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Index exists: idx_orders_cust_date_total(customer_id, order_date, total)
-- Query only needs these columns:
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 12345;
 
-- Optimizer chooses clustered index scan (fetches all columns)
-- But covering index avoids table access entirely!
 
-- PostgreSQL (with pg_hint_plan):
/*+ IndexOnlyScan(orders idx_orders_cust_date_total) */
SELECT customer_id, order_date, total
FROM orders WHERE customer_id = 12345;
 
-- Oracle:
SELECT /*+ INDEX(orders idx_orders_cust_date_total) */ 
    customer_id, order_date, total
FROM orders WHERE customer_id = 12345;

Scenario 3: Correlated Statistics

Columns are highly correlated, causing severe cardinality misestimation:

correlated-columns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- State and city are correlated
-- If state = 'CA', city is likely 'Los Angeles' or 'San Francisco'
-- Optimizer assumes independence: P(CA) × P(LA) = very small
-- Reality: P(CA AND LA) = not that small!
 
SELECT * FROM customers
WHERE state = 'CA' AND city = 'Los Angeles';
 
-- Optimizer estimates 100 rows, actual is 50,000
-- Chooses nested loop join when hash join is better
 
-- Force better join method 
SELECT /*+ USE_HASH(customers) INDEX(customers idx_state_city) */ *
FROM customers
WHERE state = 'CA' AND city = 'Los Angeles';
 
-- Better long-term solution: multivariate statistics
-- PostgreSQL 10+:
CREATE STATISTICS stats_state_city ON state, city FROM customers;
ANALYZE customers;

Summary: Using Index Hints Wisely

Index hints are powerful tools that should be used with care and deliberation. Let's consolidate the key principles:

Key Takeaways

•Hints are a last resort — Always try better statistics, index design, or query restructuring first
•Different databases, different syntax — MySQL, PostgreSQL (via extension), SQL Server, and Oracle all have unique hint mechanisms
•Hints can select indexes or access methods — You can force which index AND how it's traversed
•Hints become stale — Data changes; hints that helped yesterday may hurt tomorrow
•Hints hide root causes — They may mask problems better solved through proper optimization
•Document and review — Every hint needs justification, documentation, and periodic review
•Silent failures are dangerous — Most databases ignore invalid hints without error

What's Next

Having explored index hints for forcing index selection, the next page examines covering indexes—a powerful technique where the index itself contains all columns needed by the query, eliminating table access entirely. Covering indexes often eliminate the need for hints by making the correct choice obvious to the optimizer.

Page Complete

You now understand index hints across major database systems, when they're justified, the risks they carry, and best practices for their use. Remember: a hint is a maintenance commitment. Use them sparingly, document them thoroughly, and review them regularly.