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?