MySQL 8 Cookbook

Using index hints

Using index hints, you can hint the optimizer to use or ignore indexes. This is different from optimizer hints. In optimizer hints, you hint the optimizer to use or ignore certain optimization methods. Index and optimizer hints can be used separately or together to achieve the desired plan. Index hints are specified following a table name. 

When you are executing a complex query involving multiple table joins, and if the optimizer is taking too much time in evaluating the plans, you can determine the best plan and give it a hint to the query. But make sure that the plan you are suggesting is the best and should work in all cases.