Intermediate LevelSQL & MySQL
How do FORCE INDEX and USE INDEX hints override MySQL query optimizer decisions?
❓ Interview Prompt: Explain `FORCE INDEX`, `USE INDEX`, `IGNORE INDEX`, overriding suboptimal cost-based optimizer choices, and maintenance risks.
When the MySQL Cost-Based Optimizer incorrectly selects a full table scan or suboptimal secondary index, developers can override its choice using 'FORCE INDEX':
Syntax:
'SELECT * FROM table FORCE INDEX (idx_name) WHERE ...'
•
⚠️ Maintenance Risk: Hardcoding index hints creates technical debt. If the index is later renamed or dropped, queries will fail!
Code Implementation Example
sql
-- Forcing MySQL optimizer to use specific index
SELECT id, email, created_at
FROM users FORCE INDEX (idx_users_created_at)
WHERE created_at >= '2026-01-01'
ORDER BY created_at DESC;💡Forcing B-Tree index usage with FORCE INDEX hint in MySQL.
🎯 Interview Key Takeaway
Use FORCE INDEX sparingly as an emergency patch while investigating stale table statistics.
#MySQL#FORCE INDEX#Optimizer#Hints#Intermediate
Back to all SQL & MySQL Questions→