WordPress now powers more than one-third of the top 10 million websites. With a simple installation framework and straightforward customization tools, the rapid growth of this CMS makes sense — both large enterprises and SMBs alike benefit from the usability and adaptability of WordPress deployments.
The challenge? Ensuring your WordPress site keeps pace with user expectations for speed and accessibility: Recent data suggests that three-second load times are enough to drive potential viewers somewhere else. Even if you’ve got high-quality, regularly-posted content, the rapid uptake of mobile devices and cloud-driven connections means that users aren’t willing to wait around.
The most common cause of slowdown is your database, which stores every piece of information contained on your WordPress site. Over time, this problem naturally worsens. The more posts, comments and images you add the longer it takes your database to find and load specific information, increasing the distance between user requests and webpage displays.
In this piece, we’ll tackle the basics of WordPress database optimization. You’ll learn how the CMS stores and accesses your data, why slowdown occurs, and what steps you can take to boost WordPress performance.
Let’s get started.
WordPress Database Basics — PHP and MySQL
First stop? The basics.
WordPress uses PHP — a recursive short form of PHP: Hypertext Preprocessor — to store and retrieve data your site. The official PHP website describes it as “a widely-used open source, general-purpose scripting language that is especially suited for web development and can be embedded into HTML.”
Here’s a basic example:
echo "Hi, I'm a PHP script!";
This is the biggest advantage of PHP: It’s easy to use, simple to integrate and powerful enough to customize your WordPress page.
Along with PHP, WordPress uses the MySQL relational database management system. This is also an open-source tool, and acts at the storage repository for all data on your WordPress site. The “SQL” acronym stands for “structured query language”, which is the most commonly used database management format. In addition to data storage, WordPress also uses SQL to manage login and access requests to ensure the right data is served to the right users at the right time.
Put simply, PHP makes the requests and MySQL does the hard work by finding, retrieving and sending along the information.
MySQL: Let’s Table This Discussion
MySQL is a relational database management system, which means information is separated into tables rather than stored in a single large stack. Rules are created which govern the interaction of each table both with the management tool itself and other tables, making it easier for WordPress to find the content you’re looking for, faster. WordPress has 12 core tables, diagrammed below:
wp_commentmeta — All comments including metadata are stored here.
wp_comments — Comments made within your WordPress site are stored in this table.
wp_links — This table contains information related to on-site links.
wp_options — Options data set in Administration > Settings are kept here.
wp_postmeta — Post metadata occupies this table.
wp_posts — Your core data — all posts — are stored here.
wp_terms — Categories for posts, links and tags form this table.
wp_termmeta — Term metadata is kept here.
wp_term_relationships — This table contains information about post categories and their relationships.
wp_term_taxonomy — Contains information about the taxonomy of each wp_terms table entry.
wp_usermeta — This table collects user metadata information.
wp_users — The list of all site users is contained in this table.
Other tables are also created for any plugins you install on your WordPress site.
More data, more problems?
When you make posts, add new plugins, and gain new users, the amount of data stored in your WordPress MySQL rapidly increases. As a result, the amount of time it takes for your site to return MySQL requests also goes up. Eventually, users will experience a noticeable delay between clicks and website outcomes.
The easiest way to solve this problem is by cleaning your tables — removing duplicate data and deleting content you no longer need. While it’s possible to do this manually by examining every post and comment, this is time and resource-prohibitive.
Your simplest, speediest option? Use a plugin.
Backing It All Up
Before starting in on database speed increases, make sure your WordPress site is fully backed up. If something goes wrong and you lose critical CMS content, you don’t want to start from scratch.
This plugin lets you create a complete backup of your site that can be stored locally or in the cloud. It can also be configured to automatically backup your data to storage sites including Dropbox, Google Drive, Amazon S3 or an FTP server. The basic version of UpDraftPlus is free.
This tool is available with common control panels such as cPanel or Plesk. You’ll find it in the “database” page, and it allows you to directly edit or modify MySQL data. It’s worth noting that this tool provides direct database access — meaning that if you accidentally delete or modify something, it can’t be undone. This is why site backups always come first.
This plugin adds a settings page at Dashboard > Settings > WP Clean Up that lets you quickly remove all posts tagged as revisions, drafts or auto drafts in addition to deleting moderated, spam or trash comments.
The speed of your WordPress site matters. Users won’t wait for pages to load; they’ll simply find somewhere else to go. Optimize your MySQL database with powerful plugins — and regular backups — to boost WordPress performance.
Originally published Feb 7, 2020 1:00:41 PM, updated February 07 2020