How to Complete a MySQL Performance Tune (4 Key Tips)

MySQL is one of the most popular database management systems for web servers. If you’re running a WordPress website, there’s a good chance you use MySQL for your database. That makes it essential for you to understand how to complete a MySQL performance tune.

By “tuning” MySQL, we mean optimizing your database so that the software can respond to queries faster. Ultimately, that can lead to shorter load times and a faster overall website. Also, if you maintain your database properly, it should continue to deliver high performance even as it grows in size.

In this article, we will provide a brief overview of what MySQL is and how it works. We will then explore how to perform MySQL performance tuning using four essential tips.

Let’s go for it!

What is MySQL and how it works

Dynamic websites store information in databases, a fancy term for a complex structure of tables that contain massive amounts of information. Databases allow you to store variables and “query” them to get the information you need. For example, when you upload a WordPress website, the Content Management System (CMS) will connect to your database to get the information it needs.

If for some reason WordPress cannot connect to the database, you may run into errors like “MySQL Server Has Gone Away” or “MySQL 1064”. MySQL is open source software that allows you to create and manage databases. It is far from the only database management system available online. However, it dominates in terms of market share, with around 15% of all websites using it.

There are different approaches to database administration, and MySQL uses “relational” and “client-server” models. This is what that means:

Relational databases — This type of database divides information into tables. Thanks to the relational model, you can connect data from multiple tables using “keys” or unique identifiers.
Client-server model — With this model, your database resides on the server and your website is the client. Browsers make requests to your website, which in turn queries the database.

Since MySQL is open source software, there are many forks available. Here at Phluit, for example, we use MariaDB, which is a fork of MySQL. We chose to use MariaDB because it offers improved performance over its main software.

If you’re a Phluit user, you don’t have to worry about performing a MySQL performance tune up while we handle it for you.

Learning how to make MySQL run faster on your own is still a valuable skill, however, especially if your web host doesn’t take care of the optimization for you. Let’s talk about what that optimization entails.

4 Tips to Complete a MySQL Performance Tune

If you use WordPress and want to learn how to make MySQL run faster, your best bet is not to focus on specific configurations. Instead, you’ll want to tune your database to match the way you use WordPress. Let’s talk about what that means.

1. Keep your MySQL version up to date

Most web hosts will automatically update MySQL to the latest version for you. However, if you have full control over your server, you will need to manually update your database management system. Since you’re using WordPress, you probably understand the benefits of keeping your software up to date. There are many advantages of MySQL performance tuning, such as:

The latest versions patch security holes in the software.
The latest versions of MySQL are better optimized and therefore faster.

Faster database management software directly translates to faster load times. However, it may be that you don’t know which version of MySQL you are using. If you have full access to the server, you can launch the command line and run the following command:

mysql -v

The-v will return information about the version of MySQL your server is using. You can compare that information with the latest versions on the official MySQL website to see the newest versions available. If you’re not comfortable using the command line, you can find out what version of MySQL you’re using via the WordPress dashboard.

First, access the control panel and go to Tools > Site Health. Next, open the Info and click on Database:

Depending on your version of MySQL, the software might update automatically. If not, you can use the command line to update MySQL to the latest version. If you’re using Phluit, you don’t have to worry about updating your database management software. Instead, we automatically update clients to the latest stable version of MariaDB.

2. Check for orphaned tables in your database

Often plugins and site themes that you uninstall from your website will leave data behind. That may be due to poor coding standards, or because plugins/themes decide to store that data in case you want to reinstall them. The idea behind this approach is that leaving the settings and configurations stored in your database will save you time and work in the future.

However, all those unused tables can lead to a bloated database, which in turn can slow down queries depending on the capabilities of your server. Ideally, you’ll clean up after uninstalling plugins so you can prevent orphaned tables and data from accumulating. There are two ways you can lead to the task: manually or using a plugin.

Once you are inside phpMyAdmin, you can use the software’s Find feature to find orphaned tables related to specific plugins. The problem with this method is that it can be hard to know which queries to use unless the plugin you’re trying to clean up after has full documentation.

For example, Yoast SEO recommends using the wpseoquery to find tables related to the plugin after uninstalling it. Running a quick search using phpMyAdmin will return a list of entries containing that term in different tables:

Although the manual method works, we recommend that you use a database cleanup plugin solution. With some of these plugins, you will even be able to find orphaned tables from the control panel and delete them without having to search in phpMyAdmin.

3. Determine what mySQL data is automatically loading

Every WordPress database includes a wide range of tables. One of those tables is called wp_options, and it contains information like:

Configuration of plugins and themes
Your site URL, blog name, description, and more

Out of the box, WordPress should only automatically load critical data. However, some plugins and themes add data to wp_options and set it to load automatically. Over time, that automatic data loading can slow down response times, just like if you have too many programs set to start automatically when you turn on your computer.

If your WordPress website feels slow and you see a lot of plugin-related entries in wp_options, we recommend that you check how much data WordPress automatically loads and identify which plugins you can afford to disable.

4. Clean reviews, drafts, mangled comments and posts

As you use WordPress, your website will accumulate a lot of unused data. For example, WordPress automatically saves multiple revisions and drafts for your posts, depending on how many changes you make to them. Over time, all that data can bloat your database, so it’s a good practice to clean up those entries periodically. The best way to do this is with a plugin, like Advanced Database Cleaner:

It is not necessary to use the premium version of the plugin for this task (unlike when dealing with orphaned tables). Once you enable the plugin, you can go to the WP Database Cleaner and look at the General Cleanup section. There, you will find a complete list of all transient data that the plugin can help you remove from the database:

You can select which tables to “clean” or empty, then run the process manually. Alternatively, the plugin allows you to schedule cleanups for tables of your choice and set them to run periodically. That last option is your best bet, it means you’ll have one less task to worry about. However, it is recommended that you do not reconfigure database cleanup to run too frequently. That way you’ll still have access to earlier drafts in case you need them.

Summary

The benefits of MySQL performance tuning are many. However, the main reason you’ll want to optimize your database is to increase its performance, so your website always loads as fast as possible. Since you are using WordPress, there are many ways to perform MySQL performance tuning without changing the software configuration. If you’re interested in learning how to make MySQL run faster, here are four tips to get you started: