SQL Query Picks Wrong Index? Force It With OPTIMIZE

Yeah, that sucks when the optimizer screws up.

You've got a query that ran fine last week, and now it's crawling. The optimizer picked the wrong index. I've seen this on SQL Server 2016, MySQL 8.0, and PostgreSQL 13. Don't waste time guessing. Here's the fix.

Quick Fix: Force the Index

In SQL Server, use a query hint. In MySQL, use FORCE INDEX. In PostgreSQL, you can't force directly—use SET enable_seqscan = off or an index-only scan hint via pg_hint_plan extension. But for the love of all that's holy, this is a bandage, not a cure. Do it to get running, then fix the root cause.

-- SQL Server
SELECT * FROM Orders WITH (INDEX(IX_OrderDate))
WHERE OrderDate > '2024-01-01';

-- MySQL
SELECT * FROM Orders FORCE INDEX (idx_order_date)
WHERE order_date > '2024-01-01';

That'll work immediately. But you're not done.

Why the Optimizer Chose the Wrong Index

Three culprits, in order of likelihood:

1. Stale statistics. The optimizer estimates row counts based on histograms. If those are outdated—say, after a bulk insert or data purge—it'll guess wrong. On SQL Server, run UPDATE STATISTICS tablename;. On MySQL, ANALYZE TABLE tablename;. Check the last update with sys.dm_db_stats_properties on SQL Server or SHOW TABLE STATUS on MySQL.
2. Parameter sniffing. SQL Server caches query plans based on the first set of parameters it sees. If that first call had a rare value (like a date far in the future), the plan optimized for that. Later calls with common values get stuck with a lousy plan. Fix: add OPTION (RECOMPILE) to the query or use OPTION (OPTIMIZE FOR UNKNOWN).
3. Cardinality estimation model. SQL Server 2014+ uses a new CE by default. Sometimes the old CE (2008-era) works better for certain schemas. Change it with OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')). I've had to do this on a few OLTP systems with deeply nested joins.

Less Common Variations

Index Fragmentation

If an index is heavily fragmented (above 30%), the optimizer might skip it because scanning it costs more than a table scan. Rebuild or reorganize the index. Check fragmentation with sys.dm_db_index_physical_stats on SQL Server or SHOW INDEX FROM tablename on MySQL (look at Cardinality column).

Composite Index Column Order

You created a composite index on (colA, colB), but the query filters on colB alone. The index is useless for that filter. The optimizer correctly ignores it. Create a separate index on colB.

Missing Indexes

Sometimes the optimizer picks a bad index because there's no better one. Check missing index DMVs on SQL Server (sys.dm_db_missing_index_details) or slow query log analysis on MySQL. Don't just throw indexes at it—analyze the workload first.

Prevention: Stop It from Happening Again

Schedule statistics updates nightly on tables that change a lot. I use Ola Hallengren's scripts on SQL Server—solid as a rock. On MySQL, set innodb_stats_auto_recalc = 1 (default on 8.0). For PostgreSQL, autovacuum handles it—just make sure it's running.

Also, parameterize your queries consistently. Use stored procedures or sp_executesql with exact parameter types. Avoid ad-hoc SQL with inline values—that's a recipe for plan cache pollution.

Lastly, when you create indexes, test them with real query patterns. Use SET STATISTICS TIME ON and SET STATISTICS IO ON in SQL Server to see logical reads. If a query scans the whole index, the optimizer did you a favor—fix the index design.

One more thing: if you're on SQL Server and using FORCESEEK or FORCESCAN hints, those override the optimizer entirely. Use them sparingly—like a scalpel, not a hammer. I've had to rip out dozens of those from legacy apps because a data change broke the assumption the hint was based on.