Is it a good practice to use index hints in production code?

Question

I came across a situation where one of my MySQL (5.6) views (type - MERGE) was performing quite bad in order to fetch records (~3500 rows) for an entity id say 123.

This view performs inner joins across a few tables in order to get these records.

Surprisingly, the view returns results in ~200ms on my local docker MySQL container but takes ~10s on an AWS MySQL RDS instance.

On viewing the query plans in both environments, I found that the join order was different because the index utilised for one of the tables in the join was different (unique index is used by RDS but PRIMARY by docker mysql).

I tried a few things such as rebuilding indexes and rebuilding table stats using the analyse command on all concerned tables but all in vain. The connection collation is also the same as the table collations. The DB engine is InnoDB.

Moreover, the data on both environments is also identical as they have been setup using the same dump. I have also verified that the indexes are the same in both environments.

The only way I can see running the query optimally on RDS is by using an index hint and that performs as expected.

In such a case, is it okay to use index hints in production code?

Or do we have any suggestions on how to troubleshoot this issue?

Answer 1

As a general rule, you should only use index hints where absolutely necessary, and no other mitigation (e.g. writing your query slightly differently or rationalising down the number of indexes you have on the table) has worked reliably.

The main reason why you should avoid it is because it requires explicitly naming an index in your hint, and if the index subsequently gets removed or renamed, your query will stop working completely (it will error out because it cannot find the named index). This creates hidden pitfalls for future DBAs and maintainers working on the codebase.

Answer 2

Although your question is about MySQL, Brent Ozar has a good article on this topic about SQL Server.

Generally you want to avoid it for a few reasons, data/stats/indexes can change in the future and then using that index would be bad and hurt performance instead. But if nothing else will solve this, then you can always use it and document it for the future.

I've used RDS a few times and it can be night and day different than using a database on a machine I've noticed. Are you using one for development and one for production? Or what's the different uses for the two? And do both have the same amount of CPU/RAM?

Answer 3

As the other Answers say, avoid hints. What may help today, may hurt tomorrow when the data distribution changes.

But now what to do?

If you provide us the query, its EXPLAIN, and SHOW CREATE TABLE/VIEW, we may be able to improve the performance without using hints. Meanwhile, here are some generic comments:

• VIEWs have not had very serious development within MySQL; try to avoid them in cases where optimization is an issue.
• For some queries, a 'composite' index is a quick and easy optimization.
• 5.6 is two major releases old, consider upgrading.