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

1. Select the database (wp_environmentname)
2. Check the boxes next to tables you wish to optimize
3. At the bottom, in the dropdown, choose Optimize table

---

Table Storage Engines and Memory

WP Engine only supports the InnoDB table storage engine and any tables using MyISAM will be converted automatically. The storage engine your site uses will make a big difference in performance due to the way they write data and use server resources, and we’ve found that InnoDB consistently performs better.

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, you can manually convert the table to InnoDB using this query. 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.

WordPress 6.6+ improves the way autoloaded data is handled in order to increase website performance automatically. Excess autoload data is responsible for many performance issues, often resulting in 502 errors. By default, autoloaded options beyond a certain size threshold will automatically have their autoload capability disabled by WordPress. Learn more about this change here.

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

---

Adding Indexes To Your Database

Database table indexes are data structures that enhance the speed of data retrieval in a database. They allow access to specific rows in a table without needing to scan every record.

Adding indexes to custom tables in your database can help to improve the speed of your queries which in turn, speeds up the loading time for uncached requests to a web page. Adding indexes could be a good fit for you if you have custom created tables that store large amounts of data.

Default WordPress tables are already well indexed but some plugin authors may not have added indexes for custom plugin tables. This means you may have to review plugin tables in addition to reviewing any custom tables created by your development team when looking for potential optimization wins.

Using WordPress custom post types (which will have some indexes by default) whenever possible, instead of creating custom tables is one good way to ensure that indexes aren’t forgotten.

Methods for adding indexes

You can use the web based phpMyAdmin editor, or use wp-cli commands via SSH to add indexes to tables.

phpMyAdmin

1. Click the phpMyAdmin link from the top right of an environment’s Overview page in the User Portal. You can find detailed access instructions here.
2. Click on the database name in the left column of phpMyAdmin. It will be in the format of wp_<install_name>.
3. Select the table name in question from the list of tables.
4. Click the Structure tab for the table at the top.
   
5. Scroll down to the list of current Indexes shown for the table and click the Go button to add a new index.
   

We’ve added some Resources below if you need guidance on filling out the fields to create a new index.

WP-CLI via SSH

1. Connect to your environment via SSH. You can find detailed connection instructions here.
2. Make sure to move into the root directory for the environment from your terminal. Example: cd sites/environment_name.
3. From the root directory of your environment you can run a wp db query  wp-cli command to add an index with an SQL statement instead of using the GUI form in phpMyAdmin.
4. You can see a list of current indexes for a table with an example wp-cli command like this for the wp_posts table.
   wp db query 'SHOW INDEXES FROM wp_posts;'

We’ve included some Resources below with more details about how to format an SQL command to add an index.

Resources for Adding Indexes

Here are some resources from the MySQL docs about indexes:

• Optimization and Indexes
• CREATE INDEX Statement
• Optimizing Queries with EXPLAIN

Additionally Iain Poulson wrote a great article about SQL Query Optimization.

---

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 Caching
4. Locate Object Caching
5. Toggle Enable object caching

---

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:

• WP Engine Smart Search
• 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