MySQL Queries Not Using Indexes

[alexma418]

Issue Summary

This is not a bug report but a MySQL diagnostic warning that potentially indicates less-than-optimal MySQL performance in Ghost.

I run a self-hosted instance of Ghost with all dependencies installed in the same virtual machine. MySQLTuner-perl continually (since installation 4 months ago, through multiple versions of each Ghost component and tweaks to MySQL configuration) warns about "Joins performed without indexes". Over the last month MySQLTuner-perl reports >13,000 joins performed without indexes. My research (Stack Overflow) indicates this metric is based on MySQL code not using indexes for lookups/operations, and that it does not depend on how MySQL is configured. From other reports about "Joins performed without indexes", others warn that performing lookups without indexes can cause website performance degradation, especially as databases grow. (See answer #1, part 4 on Server Fault: "How to tune MySQL server when big peak of usage is expected")

I know that speed and performance is one of Ghost's selling points, so I wanted to bring this up in case adding indexes to Ghost's database operations could positively impact future performance.

Steps to Reproduce

1. Install a self-hosted version of Ghost on Ubuntu 20 LTS with MySQL 8 as per standard directions
2. Create a Ghost blog using the default Casper theme (using 4.6.0)
3. Generate traffic to the Ghost instance
4. Install MySQLTuner-perl
5. Run sudo perl ~/mysqltuner/mysqltuner.pl -host 127.0.0.1

Ghost Version

4.30.1

Node.js Version

v14.18.2

How did you install Ghost?

Ubuntu 20.04.3 LTS

Database type

MySQL 8

Browser & OS version

No response

Relevant log / error output

No response

Code of Conduct

• [X] I agree to be friendly and polite to people in this repository

[github-actions[bot]]

This issue is currently awaiting triage from @ErisDS. We're having a busy time right now, but we'll update this issue ASAP. If you have any more information to help us triage faster please leave us some comments. Thank you for understanding 🙂

[alexbaileyuk]

MySQL indexes have benefits and drawbacks like most relational DB's. Not everything needs an index and it won't solve every problem. On instances with low memory allocation, it might not help at all. You have to be especially careful about tools and articles online because they can suggest blanket solutions which may not be relevant in all cases.

@thebertian Does the tool give you any examples of the queries which are not using indexes?

[github-actions[bot]]

Note from our bot: The needs info label has been added to this issue. Updating your original issue with more details is great, but won't notify us, so please make sure you leave a comment so that we can see when you've updated us.

[alexma418]

@alexbaileyuk - MySQLTuner-perl doesn't report which queries aren't using indexes. It just gives a count: [!!] Joins performed without indexes: 13648

My website has an HTTP ping monitor on it but otherwise receives very little traffic. I assume the large count is being triggered by a query that runs every time the homepage is loaded.

Not everything needs an index and it won't solve every problem. On instances with low memory allocation, it might not help at all. You have to be especially careful about tools and articles online because they can suggest blanket solutions which may not be relevant in all cases.

I opened the ticket this issue because I didn't know if the warning was significant. My ghost installation is running flawlessly.

[github-actions[bot]]

Hey @thebertian 👋

Our team needed some more info to get to the bottom of this, however we've not heard back from you. We're going to close this for now, but let us know if you manage to dig up some more info and we'll reopen.