Home > Articles > Data > SQL

  • Print
  • + Share This

Forcing Indexes

Many systems permit overriding the optimizer's choices by forcing use of a specified index. This is a good tool for testing, particularly when you are exploring how the optimizer behaves. It gives you a way to see how different indexes work with the same data.

However, hardwiring your queries to use a particular index is dangerous. You lose flexibility. When the data changes, you're tied to the index that worked best last Tuesday—quite possibly not the right choice today. If you decide to force index choices, be sure to document the decision and the cause in the code, and plan to check the SQL regularly. There are plenty of stories about queries that were slow until an expert was called in. Performance improved!. . .for a while. . .and then dropped to abysmal. Another expert removed the index forcing elements, cleaned up some messy WHERE clauses, avoided a view, eliminated a sort, and ended up with longer-lasting improvements.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.