Databases are an important piece of your WordPress site. Your site’s database holds information like: which content is associated with which page, plugin and theme settings, users and user capabilities, eCommerce order details, and much more. While PHP code tells your site how to function and CSS tells it how to look, the database holds all the information you’ve put into your site. So communication with the database is a necessary step when generating a page for your users!
Why IS Database Optimization Important?
Because of its role in page generation on your site, the way you communicate with your database is very important. The SQL queries used to get and update information in your database should be optimized to do so as quickly as possible. By optimizing both the data in the database and the SQL queries communicating with it, you’ll improve your site load time, Time to First Byte, and overall user experience.
One of the performance optimization options WP Engine offers is the Object Caching layer. Object Caching helps your page generation time by storing the results to repeated queries for faster access. This layer of cache will store queries wrapped in the wp_cache() functions defined by WordPress, as well as Transients.
Since much of the time to generate a page is often from database queries, using Object Cache can easily help performance for many sites. To enable Object Caching, select your install name in the User Portal and click “Utilities” from the left-hand navigation. There will be an option to enable or disable Object Cache on this page.
In WordPress, the database holds many of your site’s settings. These settings can include a list of active plugins, your active theme, your site’s URL, theme settings, plugin settings, and more. The wp_options table in your database is where these settings are stored. Sometimes themes and plugins like to store extra items there, like Transients, long lists of redirect rules, and other lengthy settings. In this table there is a column labeled “autoload” which generally tells WordPress: does this setting need to be loaded on every page by default? If the answer is “yes” for some of these extremely lengthy settings, that’s extra data and bytes that WordPress has to load on your pages. Too much content set to “autoload” will lead to high Time to First Byte, and slow query performance in general.
Use the query below to find out how much data is being autoloaded on your site, and which autoloaded rows are the longest:
SELECT LENGTH(option_value),option_name FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 20;
Another conflict when autoloading a large amount of data happens specifically when Object Cache is enabled. The Object Cache loads all autoloaded data as a single row, and there is a finite amount of data that it can hold in one row. Object Cache will reject the row for being too large when a page loads, but since WordPress requires that data to load a page, it sends it back and causes a loop that eventually ends in a 502 error. Our Object Cache article goes into more detail on how to resolve this specific error.
If your site has tens of thousands of posts, chances are you’re experiencing slow search performance with the normal WordPress search function. The reason for this is two-fold: firstly, search queries to your database are having to examine millions of rows to return search results that match. And, the query that WordPress uses by default does not scale well once your site hits a few thousand posts. It can take multiple seconds to perform the search operation, and if you have multiple people searching on your site at once this can easily lead to slow performance.
With that in mind, if your site has thousands of pages, posts, and uploads it’s best to use an offsite search solution, such as ElasticPress or Algolia. This offloads the strain of performing that search to a separate system fully optimized to handle it, and returns better results faster. There is also the added benefit of freeing more server resources to handle more concurrent traffic, making your site more scalable.
Table Storage Engines and Memory
In MySQL there are two primary Table Storage Engines: MyISAM, and InnoDB. The Table Storage Engine your site uses can make a marked difference in performance. WP Engine always recommends using the InnoDB Table Storage Engine, for good cause. The MyISAM engine performs just fine for database read operations. But when it comes to writing new data or updating data in the database, MyISAM locks the full table while that write operation is taking place, meaning other simultaneous writes to that table are locked out in the meantime. InnoDB by contrast only locks the single row being written. This makes MyISAM less ideal for production/live sites.
The other primary reason to use InnoDB as your Table Storage Engine relates to Memory usage on your server environment. MySQL allocates a specific pool of Memory called the InnoDB Buffer Pool to be used by InnoDB tables. Tables which use MyISAM are not able to use this Memory pool, which means they are automatically writing to disk (swap) instead of normal Memory on the server.
If you’re unsure which Table Storage Engine your database tables are using, it’s simple enough to check. Just log into your User Portal and select your install name. Then click phpMyAdmin from the left-hand navigation. Select your database from the list at the left, and look to the Type column. If you see MyISAM tables in the list, you can change them to InnoDB easily. Just click the SQL tab and run the following query for each MyISAM table:
ALTER TABLE table_name ENGINE=InnoDB;
Be sure to replace table_name with the name of the table you want to change to InnoDB.
A quick way to help clean up your database is to look for “orphaned” data. MySQL is a “relational database” system, meaning data in one table will often correlate to data in another. A good example of the relational aspect of your WordPress database is to look at the wp_posts and wp_postmeta tables. In wp_posts, each post, page, and image has a post ID in the column labeled “ID.” In wp_postmeta, this correlates to the “post_id” column, so that specific pieces of metadata are associated with specific posts. Cleaning up orphaned data means deleting those pieces of metadata that are associated with a post ID that no longer exists. Click the SQL tab in phpMyAdmin to run the following queries and clean up orphaned metadata.
Check if your site has any orphaned postmeta:
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;
Delete any orphaned postmeta:
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
Check if your site has any orphaned commentmeta:
SELECT COUNT(*) as row_count FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
Delete any orphaned commentmeta:
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
Periodically using the Optimize Table command is a good practice for database performance and health. Optimize Tables will recreate the selected table and remove any excess disk space being utilized by that table. By freeing the excess disk space being utilized, this helps your site’s performance by reducing the amount of data that has to be stored in Memory when the tables are accessed.
To optimize tables, simply click your install name in the User Portal and select phpMyAdmin from the left-hand navigation. Then choose your database from the left side, and select the checkboxes next to the tables you want to optimize. At the bottom, choose “Optimize” from the dropdown menu.
If you’ve tried many of the options above to help optimize your database performance and you’re still experiencing issues, it may be time to upgrade your plan to allow for more server resources. In particular, if your databases total more than 1-2GB it’s a good idea to reach out to your Account Manager to ensure your site is on the right server solution for your database needs. If you’re unsure of who your Account Manager is, just reach out to our Support team in the User Portal via 24/7 chat and we’ll help you get that conversation started.