2

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?

Improve this question
0

3 Answers 3

Reset to default
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.

Improve this answer
2
  • Is refactoring becoming harder really the main reason for avoiding manual indexes? Isn't there some more serious implications like efficiency of queries?
    – Klesun
    Sep 4, 2021 at 15:33
  • @Klesun There is no efficiency overhead caused by using index hints.
    – Gordan Bobić
    Sep 4, 2021 at 19:49
0

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?

Improve this answer
1
  • For development, I am using docker mysql and it runs locally on my macbook where my application code is running. For production, I am using AWS RDS. My mac runs on 16GB of RAM. AWS RDS is much larger with 61GB of RAM.
    – Kushan Sen
    Oct 28, 2020 at 13:28
0

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.
Improve this answer
6
  • Thank you for your comments. The issue was happening due an IN clause in WHERE which was causing a very long fetch time. As I am not sure how to fix the fetch time issue, I removed the IN clause from the query and applied a filter in the application code. I tested the same on the problematic data set and the performance is a lot better (is in millis) and is acceptable. The query plan in the docker instance was different because one of the index stats was inaccurate. On performing analyze table, the query plan on docker turned out to be the same as that of RDS.
    – Kushan Sen
    Oct 29, 2020 at 17:02
  • You have found one of the rare cases where ANALYZE TABLE is useful in InnoDB.
    – Rick James
    Oct 29, 2020 at 17:10
  • I agree. Out of curiosity, any suggestions on how I can troubleshoot the high fetch time issue?
    – Kushan Sen
    Oct 29, 2020 at 18:38
  • @KushanSen - What I do... (1) use the slowlog to identify the query (unless I already know which query is at fault). (2) I ask an expert (myself) for help. Some of that expert advice is spelled out here: mysql.rjweb.org/doc.php/index_cookbook_mysql . I frequently troll stackoverflow.com tagged [mysql] [performance]. (That site is better for query questions; dba.stackexchange is better for DBA-type questions.)
    – Rick James
    Oct 29, 2020 at 21:55
  • @KushanSen - And... Just identifying the "worst" query is sometimes sufficient. It focuses you on that one query, causing you to ponder other ways to achieve its goal. While I sometimes can speed up a query by 10x with indexing or rewriting, it may be possible to radically change a query, combine queries, or even get rid of the query. That, of courses, requires an understanding of the data flow in your app.
    – Rick James
    Oct 29, 2020 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.