When WordPress loads your website, it does that because there were some super-specific bit-by-bit instructions on what to do. Where does all such information live? In databases. And if your site doesn’t perform as well as you wish it to, it may be that you need MySQL database optimization.
Databases are the storage units needed to keep your WordPress data in a structured form. Any action to be carried out consists of a request and some resulting output. Loading a website then implies sending a request (a query) to the database and displaying the information found.
For WordPress sites, all the backend files reside in the databases managed by MySQL.
In case you’re not familiar with it, we’ll tell you in a few words. If you’d rather skip right to the MySQL database tuning, click here to jump to the most interesting part.
What is MySQL
This is a database management system used in WordPress. It is one of the most widely implemented software used for handling lots of tables of data. It got its name from the language used for the queries – SQL, or search query language. These two are not to be confused: SQL (a language) is the means of communicating with MySQL (a database management system).
This database management system stores and handles all data from your WordPress site. Naturally, if there’s an issue with your database, it can result in some sensible trouble for your entire site. That’s why you need to make checking its performance your habit.
What MySQL handles
WordPress works with relational databases: those that consist of tables interrelated with each other. It is the base for your WordPress site: it keeps all the data and manages the queries to load it.
SQL queries are commands that WordPress uses to retrieve the data. After retrieval, the data undergoes further processing, and the website is loaded.
As you can imagine, it implies going through huge amounts of all sorts of rows and columns of information. But you can make the process less of a problem!
Why do we care about MySQL (or any other) databases?
So to say, a rhetorical question, but understanding the importance of the matter helps.
Same as you want to keep your website lightweight and efficient, you should maintain your database neat and lean. You do need that: and mostly for similar reasons.
It all boils down to boosting performance. If your website’s backend contains lean-coded core and content files with no bloats, it’ll perform better. Likewise, if your database has only the data it needs within sufficient but not excessive storage space, it handles the requests faster. The less space the database takes and the fewer resources it requires, the more of those remain for processing requests.
There are some page speed metrics that proper database tuning will affect directly. One of them is TTFB (time to first byte). It is the amount of time it took your browser to load the first byte of data. You could’ve seen it in the waterfall in DevTools (or anywhere you prefer running speed tests):
If the database has less data to run through, it will do it more quickly, and WordPress will load the page faster. That’s why we recommend tuning databases for better performance – and we will show you how.
Troubleshooting and fixing problems may seem incredibly complicated. However, this gets easier as you get used to it, and your profit from it will be immense.
Since the process will involve executing some queries, let’s first make sure we know how to do it if you have a WordPress site.
How to run a query
This skill will come in handy lots of times when working with a WordPress site. The whole process will take place in phpMyAdmin. Choose where you’ll be executing queries. Select the name of the corresponding database from the list on the left.
Then, click SQL (the tab above; on the screenshot, we have it opened), and you’ll see the field where you can type your queries. Provide the query and click Go to run it; then, you’ll see the results.
There is another option to do it. The Query tab provides advanced settings.
There, you can configure your query and specify what you look for and how the results should appear.
MySQL database tuning techniques
High-quality hardware
Your important checkpoint in this list is the hardware you use. If your CPU, memory, and network connection are in any way weak or inconsistent, it will most surely cause lags and errors. And yes, tuning will make the situation slightly better, but you’ll achieve the limits of the hardware capacity quickly enough that it won’t matter. Make sure your hosting provider doesn’t drag you down but, instead, ensures flawless and high-speed internet connection; we know a few ones that you’ll love.
Now, as we move on to the queries, we remind you to create a backup for your website before making any changes. This is essential and will save you a lot of time and nerve in case things go not the way you expect. Also, a good practice would be to test the edits on a staging website first. That way, your website will be safe and sound no matter what.
Optimize MySQL database tables
This is the first and probably the easiest step we recommend. The Optimize tables command is a built-in method of reducing the excess space taken up by the database.
In your phpMyAdmin, select a database (on the left). Then, select the tables you are planning to improve (you can check all of them with the checkbox below the list of tables). You will see the command you need in the menu below.
And that’s it; the tables now take up less storage. Run it on your databases one by one, and don’t forget to check if everything works well after each go.
Choose the right storage engines
To read and process the data in a database, WordPress needs an engine. Storage engines used in MySQL are InnoDB and MyISAM.
InnoDB will be a better choice from the performance standpoint. It uses its own memory pool to cache the data instead of interacting with the server directly each time. MyISAM doesn’t use the database memory, which means you may experience performance-related problems. One of the issues is table locks: when you update the data in a table, MyISAM locks the entire table so that you can’t interact with that table in any way. InnoDB, on the contrary, locks only the row on which you work.
Though starting from version 5.5, MySQL sets InnoDB as the default storage engine, some of your tables may still use MyISAM.
You can convert all the MyISAM using tables to InnoDB format. Choose a database in phpMyAdmin and find the ones you’ll be converting by sorting tables by type.
To change the storage engine, you have two options. Either you click on it, go to the Options tab and select InnoDB as the storage engine in the corresponding field.
Alternatively, you can perform the conversion by running the following query with the name of the table you’ve chosen (we used wp_filemeta as an example):
ALTER TABLE wp_filemeta ENGINE=InnoDB;Regular MySQL database cleanup
In relational databases, tables refer to each other so often and in so many ways that you can’t avoid having various obsolete or orphaned items. If you delete some data, most of the time, it doesn’t mean you’ve gotten rid of everything related to it.
– Orphaned content
There are lots of traces left in your tables after erasing some elements. This is orphaned content which is, to put it simply, a waste of storage space.
WordPress leaves them intact after the deletion for several reasons. First of all, they weren’t the object to be removed, they are only related to it. Second, taking care of removing traces is additional work for the developers which they’re not always interested in. It is not that they were negligent or meant you bad; it could’ve just been not within the scope. Also, in case you install the deleted content back, WordPress will use those traces again.
Most of the time, though, we want them to be out for good. To ensure that, check if you have any orphaned content in your database. Look for any metadata from the deleted elements. Run this query to find any postmeta (post-related information) leftovers:
SELECT COUNT(pm.meta_id) as row_count FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;and this one for the commentmeta (comment-related information):
SELECT COUNT(*) as row_count FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);If any of those appear, delete them by pasting DELETE instead of SELECT COUNT(*) as row_count in each query and executing the resulting queries.
– Pingbacks and trackbacks
Pingbacks and trackbacks make up another class of unnecessary stuff in your backend files. They are, in principle, the same thing. Both are a method of notifying WordPress users someone has linked to their post. The minor discrepancy hides in the form. While a pingback is simply a notice, a trackback is more of a comment: you manually send it with an excerpt of the text where the link appeared. Using them is typically frowned upon as there’s little benefit from them.
You can remove pingbacks by executing the following query:
DELETE FROM wp_comments WHERE comment_type = 'pingback';The same will work for trackbacks (with the only exception: you need to put trackback instead of pingback).
– Revisions
Revisions store all the drafts of your posts so that you could restore a previous version if necessary. Here’s a query to delete them:
DELETE FROM wp_posts WHERE post_type = "revision";Some providers disable revisions on WordPress by default (but it never hurts to check).
– Cookies
Remove your session data stored in the wp_session token:
DELETE * FROM `wp_options` WHERE `option_name` LIKE '_wp_session_%'– Transients
While transients are an awesome feature for speeding up your website. However, if your plugins that use transients don’t delete the expired ones, you may want to do it to clear up some space. You would need the following query:
DELETE * FROM `wp_options` WHERE `option_name` LIKE ('%_transient_%')…or you can use a plugin for it.
Cleanup plugins
Right, plugins. Removing it all by executing queries manually is a time-consuming process. It can also be confusing if you’re not a developer or a tech geek.
Of course, on WordPress, you can enjoy the privilege of carrying out the cleanup with the help of plugins. They run the clearing algorithms for you so that you wouldn’t have to touch a line of code. To optimize MySQL databases, you can use Advanced Database Cleaner, WP-Optimize, or ODAR, to name a few. They implement a lot of techniques and help you make your WordPress database as efficient as possible.
Object caching
Remember transients we mentioned earlier? They are one of the forms WordPress can store queries in the memory for faster access. Similar to page caching, Object Cache eliminates the need to refer to the source each time there’s a request.
After each query, Object Cache stores the results and sends them when the same query appears again. That way, the query doesn’t go all the way to be processed by the database management system. It means that, instead of generating the result, the already existing one loads.
If you’re using the Memcached Object Cache WordPress plugin, you already have it enabled. If you aren’t, you can enable it in your hosting settings (if your hosting supports that. Kinsta or WP Engine do support it 100%).
Autoloaded data
Here, the name speaks for itself. Your WordPress site loads some data automatically, no matter what the request is. It is stored in the wp_options table and can be disabled by a click.
Note that disabling all the autoloaded data is not the best idea. This is a helpful feature, and you’ll probably end up keeping a lot of it. However, you should analyze this data and decide what part of it is useful and which is not so necessary for every load.
The amount of autoloaded data considered to be reasonable is 0.8-0.9 Mb. To find out how large is your set, run this query:
SELECT SUM(LENGTH(option_value)) FROM wp_options WHERE autoload = 'yes';and check if the result is less than the recommended limit (note that the number you get is in bytes).
If the data exceed the limit, consider disabling the autoload for some of it. Find the row with the data you wish to disable and simply set No in the corresponding column:
And you got it done.
Helpful tools
Now that you know the what’s and how-to’s of tuning your WordPress database, you most certainly want to keep it efficient. The answer is in assessing database performance regularly.
Percona Monitoring and Management is a great open-source software that can help you do that. It provides you the opportunity to monitor the key performance metrics associated with databases (it works with different kinds of those) and offers some tools and techniques to enhance their operation.
There are more marvelous tools like those by SolarWinds or Idera Diagnostic Manager that do even greater things in terms of database performance analysis. Those, unfortunately, don’t go for free, though they do offer a free trial. So, choose which one suits you more and use it often enough to take care of all potential problems.
How to go further with the improvements
You’ve done an impressive job rectifying your WordPress database performance. However, after MySQL database optimization, there’s still more you can do.
There are plenty of advanced techniques you can exercise to boost performance even more. Just make sure you understand what you do and avoid running queries if you’re not fully aware of the purpose and potential outcome.
Work on your page speed. A high page speed score is a crucial factor for your success online. A faster loading website will have a higher conversion bringing you more money and leaving your clients satisfied.
Assessing your page speed is absolutely essential if you strive for a better performance of your WordPress website. Put it in your schedule and do it regularly, and you’ll be able to address issues as they appear without letting them meddle in your visitors’ user experience.
It is also helpful to exercise some other methods for the general improvement of your WordPress site performance. Make your visitors’ user experience seamless like no other.
Keep track of how things work. Control and prevent all suspicious events and actions – remove malware from your site. And remember: you are in charge of your success. The better your website works, the more profit it brings you.
Contact Support group
Our blog has lots of WordPress-related guides and tutorials: you’re more than welcome to check with it if any issue occurs on your website. And if there’s anything that you need to resolve, we’re happy to provide our best service.
We take WordPress development to the next level. Our professional team of the most skillful top-notch experts will thoroughly work on your problem and fix it quickly and effectively.
Feel free to contact us for support with WordPress development: we’ll do every possible thing to help you.
