If we were asked to name two of the most common issues with WordPress websites, the answer would be security and speed. And what’s the one main factor these two have in common? The database.
The database of a WordPress site contains all the valuable data and is often the bottleneck of the site’s performance, so we can safely say that the database is the single most important part of your WordPress infrastructure.
The database is the single most important part of your WordPress infrastructure.
We’ve put together this list of the 13 best tips for making sure that the database of your site runs as efficiently and securely as possible.
Tip #1 – Make (use of) DB Dumps
As the single most important part of your WordPress infrastructure is the database, naturally it should be included in your the backups of your WordPress website. However, it’s all too common to see people backing up their sites by just copying the WordPress files.
Database dump files are in a plain-text format, making it easy to view and modify them as needed, and they are also interoperable with all MySQL and MariaDB servers, allowing you to upload the file to any MySQL or MariaDB database server anywhere.
Making a DB dump from the command line is easy with the mysqldump and wp db export commands. If you’re hosting with Seravo, we automatically handle backups for you that include the database as well as the WordPress files. You can also use our tools and commands to manually take a new backup any time you want.
Tip #2 – Learn WP-CLI DB Commands
Learning to use WP-CLI is beneficial for any developer and WordPress user, but learning the database related commands is especially useful, as these tend to be some of the most powerful tools.
You can list all of the WP-CLI database commands that exist by using the command wp db --help.
My personal favourite out of all the WP-CLI database commands is the wp search-replace --all-tables command, which allows, among other things, for an easy way to replace all content that uses http in the url of the site with https. Going from http://example.com to https://example.com has never been easier.
All of our customers have free SSL certificates automatically installed on the sites they host with us. wp search-replace can be used, for example, to make sure no mixed content warnings exist on the site after migrating it to our servers.
Tip #3 – Use Adminer to Browse the Database
Adminer is a lighter and more secure alternative to PHPMyAdmin. It consists of a single PHP file and provides a graphical interface for database management. All of our WordPress sites are delivered with Adminer installed and our customers can use it to manage their databases efficiently and securely.
Tip #4 – wp_options and Autoload
When a WordPress page loads, it runs this query every single time:
SELECT * FROM wp_options WHERE autoload = 'yes'
If the wp_options table is larger than 1MB in size this query results in rather heavy database use, which means you should try to clean up the wp_options table.
To find the rows with the largest amount of data in the option_value field, use the query:
SELECT option_name, length(option_value) FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 30
If you find plugins that are polluting the options table with unnecessarily large rows, we recommend you to file a bug report with the plugin’s author.
If you can’t clean the wp_options table you should add an index on autoload:
CREATE INDEX autoloadindex ON wp_options(autoload, option_name)
However, adding an index to a small wp_options table does not make sense, because then the index might end up being either similar or larger in size than the actual table. It should only be used if the wp_options table is larger than 1MB and can’t be cleaned up.
Tip #5 – wp_postmeta Bloat
A WordPress site that uses custom post types or WooCommerce is likely to have a large wp_postmeta table. Each new post adds just one new row with multiple fields to the database and keeps the number of different column names constant, but using the add_post_meta() calls will bloat the database with tens or hundreds of rows per post, with each row containing only two fields: name and value.
This type of bloat can easily blow up the size of the database, so identifying the meta_key naming patterns with the highest number of rows is important. These patterns can be found with the query:
SELECT substring(meta_key, 1, 20) AS key_start, count(*) AS count FROM wp_postmeta GROUP BY key_start ORDER BY count DESC LIMIT 30
Tip #6 – Learn SQL
Databases build on over 20 years of engineering on how to fetch a small set of data from a huge set of data as fast as possible. You shouldn’t try to reinvent all that engineering with PHP, but learn SQL instead.
When it comes to WordPress, make sure you don’t store everything in wp_postmeta. It’s okay, and even encouraged, to create custom tables with the correct columns, relations and indexes already defined.
You should also learn more about indexes and the reasons why you don’t want to cause full table scans in the database.
Tip #7 – But Don’t Use SQL Directly
In WordPress you shouldn’t use SQL directly, instead using the PHP get_posts() for the basics, and WP_Query class for the more advanced use cases.
When WP_Query doesn’t fit your purposes, use the $wpdb set of functions designed for interacting with the database, such as $wpdp->get_row() and $wp_db->insert().
If you really need to use raw SQL, don’t access the database directly, but use $wpdb->prepare() and $wpdb->query() to avoid SQL injection vulnerabilities.
Tip #8 – Configuring the Database Server
The database server needs to be configured correctly for optimal performance and security.
MariaDB is preferred over Oracle MySQL and a recent version (10.1+) should always be used. For the storage engine choose InnoDB over MyISAM and set the character set to UTFMB4 (so you can use emojis 😉 ) When it comes to collation, be sure to set your local sorting order to A-Ö.
Then all you have to do is optimize all the other settings…
Database configuration in an optimal way is really not that simple, especially after it goes beyond the basics listed above. If you don’t want to go through all the configurations in your database, but want it optimized for the best performance and security, hiring a database administrator or choosing a managed WordPress hosting company like Seravo is recommended.
If you want to run your WordPress site on an optimal hardware and software configuration, we recommend checking out our premium WordPress hosting and upkeep plans.
Tip #9 – Transients and Object Cache
Use Redis Cache or something similar to store transients and sessions so they can be removed from wp_options. Redis and WP Transients API will ease the load on the database and helps in making your site run super fast.
Tip #10 – Monitor Performance
Monitoring the performance of a website and its database is crucial to ensure optimal performance and happy visitors on the site. At Seravo we monitor all of our customers’ sites around the clock, which means that if you host your site with us it’s likely that we’ll notice the performance bottlenecks and notify you. However, it never hurts to be prepared by knowing how to monitor website and database performance.
Directly on the database side of things there are two extremely useful ways to track the performance of your database and its queries. You can use SHOW PROCESSLIST to get an overview of all the processes and threads that are currently running. To get an insight into all the unusually slow queries you should activate the slow query log that shows all the queries that take longer to complete than the long_query_time that has been set. By default the long_query_time is 10 seconds.
To identify performance bottlenecks on a production site, enabling Tideways to sample and help debug PHP execution slowness and errors is recommended. We’ve written a more in-depth look and case study about Tideways and how to use it, which is especially useful for our customers as a Tideways integration is directly built-in to our Seravo WordPress hosting plans.
Tip #11 – Database Cleanup
Cleaning up some of the unnecessary leftovers from the database, such as old post revisions and transients, is a good way to increase the performance and security of your database. An example of how to clear old transients and revisions from posts created last year and before that:
DELETE FROM `wp_posts` WHERE post_type = 'revision' AND post_date NOT LIKE '2019-%'
DELETE FROM wp_options WHERE option_name LIKE ('_transient_%') OR option_name LIKE ('_site_transient_%');
Cleaning up the database of data from plugins that you want to remove can be done by taking a look into the plugins folder at wp-content/plugins/plugin-name and opening seeing what the uninstall.php file contains. This trick can also be used to delete unnecessary data of plugins still actively in use by locating the data the plugin would remove after it was uninstalled, and manually cleaning up some of the older data.
Tip #12 – Explain, please
Sometimes you might need a little more information about the way the optimizer is running a database query, and that’s when you can use EXPLAINto have the optimizer show exactly how it runs the query in question.
EXPLAIN SELECT * FROM wp_options WHERE autoload = 'yes'
Tip #13 – Test with Dummy Data
When developing a new site it’s always useful to populate the site with content to see how it would look like and perform when in actual use. There are three common ways to add dummy data to a site, all a little different in their difficulty level and style. Depending on the desired outcome, one or more of these solutions might come in handy.
The easiest way to do this is by importing a file created by the team behind WordPress to aid theme developers. The file is called themeunittestdata.wordpress.xml and you can find it and its more specific usage instructions in the WordPress Codex, but simply put you download the file from GitHub and import it to the site by using the built-in import tool inWordPress under the conveniently named ‘Tools’ section in the admin dashboard.
For those who want more data, the best option might be to use the WP-CLI command line tool and the command wp post generate. By combining this with curland the loripsum.net dummy content generator’s API, we’ve got a quite powerful dummy data generator in our hands.
curl http://loripsum.net/api/5 |
wp post generate --post_content --count=10
Finally there’s the wp-cli-fixtures option. This is a full-blown dummy data generator in itself with everything from installation instructions to a demo gif available in GitHub.
Bonus Tip
Never push your development database into production if you’re not a 100% sure you know what you’re doing. Usually you just end up with a bloated database on a production site, or worse, a broken database on a production site.
That’s it for this database efficiency and security post, hope you enjoyed it and as always leave any questions and comments below, and if you’re looking for premium WordPress hosting with Upkeep, do check out our plans.
10 things every developer should know about their database to run word press optimally from Otto Kekäläinen