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 that it uses MySQL for its database. That makes it essential for you to understand how to complete a MySQL performance tune.

By “tune” MySQL, we mean optimizing your database so the software can respond to queries faster. Ultimately, that can lead to shorter loading times and an overall snappier website. Moreover, if you maintain your database correctly, it should continue to offer high performance even as it grows in size.

In this article, we’ll provide a brief overview of what MySQL is and how it works. Then we’ll explore how to perform a MySQL performance tune using four essential tips.

Let’s get to it!

What MySQL Is and How It Works

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

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

MySQL market share.

There are different approaches to database management, and MySQL uses “relational” and “client-server” models. Here’s what that means:

• Relational databases — This type of database breaks information into tables. Thanks to the relational model, you’re able to connect data from multiple tables using “keys” or unique identifiers.
• Client-server model — With this model, your database resides in 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 Kinsta, for example, we use MariaDB, which is a fork of MySQL. We elected to use MariaDB because it offers improved performance over its parent software.

If you’re a Kinsta user, you’ll be glad to know that we handle MySQL performance tuning for you. Learning how to make MySQL run faster on your own is still a valuable skill, however, mainly if your web host doesn’t take care of optimization for you. Let’s talk about what that optimization entails.

MySQL is one of the most popular database management systems for web servers ... which means that it's essential to know how to complete a performance tune Click to Tweet

4 Tips for Completing a MySQL Performance Tune

If you use WordPress and want to learn how to make MySQL run faster, your best bet won’t be to focus on specific configurations. Instead, you’ll want to fine-tune your database to match how you use WordPress. Let’s talk about what that means.

1. Keep Your Version of MySQL Up to Date

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

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

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

mysql -v

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

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

Checking the MySQL version in WordPress.

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

2. Check Your Database for Orphaned Tables

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

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

Once you have a backup, you can access your database via phpMyAdmin. If you’re a Kinsta user, log into your MyKinsta dashboard and go to Sites. Select the site you want to work on and jump to its Info tab. There you’ll find a link that’ll give you direct access to your database via phpMyAdmin:

Accessing phpMyAdmin via MyKinsta.

Once you’re inside phpMyAdmin, you can use the software’s Search feature to find orphaned tables related to specific plugins. The issue with this method is that it can be hard to know what queries to use unless the plugin you’re trying to clean up after has comprehensive 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 across different tables:

Looking for entries related to Yoast SEO in your database.

Although the manual method works, we encourage you to use a database cleaning plugin solution. With some of these plugins, you’ll even be able to find orphaned tables from within the dashboard and delete them without having to dig through phpMyAdmin.

3. Determine Which Data MySQL Is Autoloading

Every WordPress database includes a broad array of tables. One of those tables is called wp_options, and it contains information such as:

• Plugin and theme settings
• Your site URL, blog name, description, and more

If you check the wp_optionstable using phpMyAdmin, you’ll notice a column named autoload. WordPress checks for rows with a yes entry under autoload, then loads that data every time someone accesses your website and visits any of its pages:

Checking wp_options for autoloading data.

Out of the box, WordPress should only autoload critical data. However, some plugins and themes add data to wp_optionsand configure it to load automatically. Over time, that autoloading data can slow down response times, just as if you have too many programs set to launch automatically when you turn on your computer.

If your WordPress website feels sluggish and you can see many plugin-related entries in wp_options, we recommend that you check out how much data autoloads in WordPress, and identify which plugins you can afford to disable.

4. Clean Up Revisions, Drafts, Trashed Comments, and Posts

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

The Advanced Database Cleaner WordPress plugin.

You don’t need to use the plugin’s premium version for this task (unlike when dealing with orphaned tables). Once you enable the plugin, you can go to the WP DB Cleaner tab and look at the General cleanup section. There, you’ll find a complete list of all the transient data the plugin can help you remove from the database:

Using Advanced Database Cleaner to clean up your database.

You can select what tables to “clean up” or empty, then run the process manually. Alternatively, the plugin enables you to schedule cleanups for the tables you choose, and configure them to run periodically. That last option is your best bet — it means you’ll have one less task to worry about. However, we recommend that you don’t reconfigure the database cleanup to run too often. That way, you’ll still have access to previous drafts in case you need them.

What does it mean to 'tune' MySQL? Think shorter loading times and a snappier website Learn how to get started right here Click to Tweet

Summary

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

1. Keep your version of MySQL up to date.
2. Check your database for orphaned tables.
3. Determine which data MySQL is autoloading.
4. Clean up revisions, drafts, trashed posts, and comments.

Do you have other tips for fine-tuning MySQL databases? Please share them with our community in the comments below!

The post How to Complete a MySQL Performance Tune (4 Key Tips) appeared first on Kinsta®.