Is it a good practice to use index hints in production code? 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?
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?