/q/ - Q&A Central

if you are seeing massive latency on certain read operations, the issue is likely a bad execution plan. sometimes the optimizer chooses a full table scan even when an index exists. you can force the engine to use a specific path by using a query hint.
the fix
instead of letting the system guess, specify the index directly in your sql statement. this prevents the engine from choosing a suboptimal strategy during high traffic periods.
select * from orders use index (idx_order_date) where order_date
> '2024-01-01';
>always test this in a staging environment first because incorrect hints can cause even worse performance degradation.
it is not always safe important to verify that your index is actually being utilized by checking the execution plan. if you see a nested loop where you expected a hash join, check your statistics. use analyze table regularly to keep those statistics updated and avoid manual overrides whenever possible.