Sample Example – SELECT * FROM table_name FORCE INDEX (index_list) WHERE condition;
Without using force index, mysql’s query optimiser decides upon the best candidate for index that can be used in the given query. But if it finds it has to still scan major percent of the rows then it skips the indexing and proceeds with full scan. Thus, it may or may not use any indexing.
So, let’s say query optimiser has a better index candidate which it can use (or even without any index) to compute the query quicker. But by forcing index, it might slow the query.
Advertisement
Answer
to make sure I don’t do this mistake […] what points should I take care of?
Why not let the query planner do the hard work?
Database vendors invest great effort in developping efficient and fine-tuned software. In an overwhelming majority of cases, the query planner makes the good decision as regard to building the ideal execution plan for a query. Performing a full scan rather than an expensive index lookup is typically one of these cost-based optimizations.
In my experience, there are very rare cases when index hints are really needed. Furthermore, index hints somehow generate technical debt, because they might negatively affect the performance of the query when the data or the structure changes in the future. That should be your last resort, not the first thing to go to.
Note: one thing to not overlook is that the optimizer needs accurate information to make good decisions. Make sure to run ANALYZE TABLE on a regular base on your tables.