WordPress Database Optimization

The database of your WordPress website holds information such as post content, users, orders and plugin and theme settings. While PHP code tells the site how to function and CSS tells the code how to look, the database holds all the information for a page’s content. It’s important for the database to be lightweight so it is agile enough to interact with every page without negatively impacting performance.

The SQL language is what’s used to request, or “query”, information from the database. It’s important that SQL queries also be lightweight as this ensures pages can load efficiently. In order to optimize your database you will likely need to run some of these queries from phpMyAdmin.

By optimizing both the data in the database and the SQL queries communicating with it, you will improve your site’s load time, Time to First Byte (TTFB), and overall end-user experience.

---

Optimize Tables

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 local storage being utilized by that table. Freeing up local storage improves performance by reducing the amount of data that has to be stored in Memory when the tables are accessed.

To optimize database tables:

1. Log in to the User Portal
2. Select the environment name
3. Click phpMyAdmin
4. Select the database (wp_environmentname)
5. Check the boxes next to tables you wish to optimize
6. At the bottom, in the dropdown, choose Optimize table

---

Table Storage Engines and Memory

There are two primary table storage engines: MyISAM and InnoDB. The storage engine your site uses will make a big difference in performance due to the way they write data and use server resources. WP Engine always recommends using the InnoDB table storage engine.

MyISAM performs just fine for database read operations, but when it comes to writing or updating data, the entire table is locked until the process is complete. This prevents any other read/write operations from starting in that table until that individual process is done. InnoDB only locks the single row being written to, freeing up the rest of the table for simultaneous operations.

Another big difference is the way both storage engines interact with memory on the server. There is 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, meaning they write to disk (swap) instead of using the database memory.

To check your storage engines:

1. Log in to the User Portal
2. Select the environment name
3. Click phpMyAdmin
4. Select the database (wp_environmentname)
5. Look for the “Type” column
   • You can sort by this column by clicking the name

Once you’ve found a table that is using MyISAM, convert the table to InnoDB. Replace table_name with the corresponding table name.

ALTER TABLE table_name ENGINE=InnoDB;

If you’re not sure how to run a query, check out our guide.

---

Database Cleanup Options

MySQL is a relational database system, meaning data in one table will often correlate to data in another. When a value, such as a post, is deleted from a table, it may leave orphaned data behind in another. Cleaning this orphaned data up can reduce the size of your database and thus improve it’s performance.

If you’re not sure how to run a query, check out our guide.

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);

By default WP Engine disables revisions due to database bloat, but your site may have had revisions from a previous host. Delete any revisions:

DELETE FROM wp_posts WHERE post_type = "revision";

Check for wp_session data:

SELECT * FROM `wp_options` WHERE `option_name` LIKE '_wp_session_%'

Remove wp_session data:

DELETE FROM `wp_options` WHERE `option_name` LIKE '_wp_session_%'

Delete expired transients:

DELETE FROM `wp_options` WHERE `option_name` LIKE ('%_transient_%')

Delete tags that aren’t associated with any posts:

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

Delete pingbacks and trackbacks:

DELETE FROM wp_comments WHERE comment_type = 'pingback';
DELETE FROM wp_comments WHERE comment_type = 'trackback';

Database Cleanup Plugins

Understandably, running a large amount of queries can be intimidating, especially if you’ve never worked with a database before. There are plugins available which can help,. however plugins may delete data more indiscriminately than when running SQL queries one at a time. Make a backup before continuing. We also highly advise testing any changes first on a staging or development environment before proceeding on live.

• WP Rocket
• WP Optimize
• WP Sweep
• Advanced Database Cleaner

---

Autoloaded Data

Certain information in the database must load on each request, such as the site’s URL and active theme and plugins. In WordPress this is called autoloaded data and it is stored within the wp_options table.

While autoloaded data is useful in some cases, it is simply not necessary for most information to be load on every single request. It’s recommended to keep your total autoloaded data below 800,000 bytes (0.8Mb) for optimal performance.

For every row in the options table, there is a corresponding value in the “autoload” column that either reads yes or no. Disabling autoload for a row is as simple as setting the value in this column to no.

Disabling autoload on a data row does not remove it from your database, it simply no longer calls the information into each page load automatically. The data is only pulled into the page when requested by a query when autoload is set to no.

Ultimately it is up to you or a developer to determine which information on your site needs to be autoloaded and which can be disabled without damaging functionality. Below we’ve included some helpful queries to manipulate autoloaded data.

If you’re not sure how to run a query, check out our guide.

Calculate the total amount of autoloaded data in bytes:

SELECT SUM(LENGTH(option_value)) FROM wp_options WHERE autoload = 'yes';

Find and sort the top 20 largest autoload value rows:

SELECT LENGTH(option_value),option_name FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 20;

Disable autoload on a specific option. Replace option_name with the actual option name found using the previous command:

UPDATE wp_options SET autoload='no' WHERE option_name='option_name';

EX: UPDATE wp_options SET autoload='no' WHERE option_name='really_large_row';

After you’ve reduced your total autoload data below 800k bytes, ensure your options table is indexed. This helps your site serve your freshly cleaned autoloaded data more quickly.

CREATE INDEX autoloadindex ON wp_options(autoload, option_name);

---

Object Cache

Object Cache stores the results to queries for faster access later. It has a buffer up to 1MB and impacts every page of your site, even pages not subject to normal page caching such as wp-admin.

Cached queries are wrapped in the wp_cache() functions defined by WordPress in order to be stored, as well as in transients.

Object cache is disabled by default, enabling it is typically recommended to improve performance.

To enable Object Cache:

1. Log in to the User Portal
2. Select the environment name
3. Click Utilities
4. Select the option to either enable Object Cache
5. Save

---

Search Solutions

If your site has several thousand posts, you’re likely experiencing slow search performance. This is due to the amount of entries being searched and the search query itself not scaling to a large amount of content.

After your site exceeds several thousand posts, it’s best to use an off-site search solution. We recommend:

• ElasticPress
• 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.

---

Options Table

At WP Engine we strive to promote best practices to make your site run efficiently. When using a theme or plugin it is important to store data in a way that is quick for WordPress to retrieve it. That is why our platform automatically removes certain large rows in the options table of your database. A large option is defined as having more than 1mb for autoload content and 4mb for non-autoload content.

Rows in wp_options with autoload set to “yes” are considered large at 1mb because all of that data has to be loaded up on every single page load. This can create a bottleneck on your website and slow every single page down.

In our findings, mainly transients are stored in the options table as non-autoload, so they can safely be removed because they’re inherently, well, transient, and will be regenerated if needed.

---

Upgrade

If you’ve tried 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. Our team is happy to take a look at your current server resources to determine if a larger server may be needed. 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.

---

NEXT STEP: Best Practices for Maximizing Availability