Optimizing WordPress databases

Overview

WordPress sites are composed of two main components: the website files and the database. The webserver files contain things like the images, the plugins and theme files, and the WordPress core files. The database contains things like the core, plugin and theme options, the post content, user content, and metatables. Online tools cannot access the database, and it's possible to have a high score on these tools white having poor database performance. The following article details several methods of optimizing a WordPress site's database.

This article will be using wp-cli to optimize your database. You will need to log into your server via SSH and navigate to your WordPress site's directory to run these commands.

Additionally, make sure to back up your database before performing any destructive action.

The options table

The options table contains important WordPress site data, such as the site URL, path, and saved settings. It also contains plugin and theme settings, along with transient (temporary) data. Over time, this table can become bloated by transient data with long expiration timers and leftover settings from uninstalled plugins.

Deleting transients

  1. Delete any transients in the WordPress database with the following command:
    [server]$ wp transient delete --all
  2. Optimize the remaining tables in the database with the following command:
    [server]$ wp db optimize

If you would like to automate these commands in the future, you can create a cron job using the DreamHost panel.

If your options table is 10MB or larger after optimization, consider using DreamHost's WordPress database optimization service, or reach out to another professional WordPress developer for more assistance.

Autoload

Autoload data is a set of data that is loaded with every request to the options table. Large amounts of autoload data can cause poor /wp-admin performance and may affect the public-facing site as well. Run the following command to find out how much autoload data your site has:

[server]$ wp db query "SELECT SUM(LENGTH(option_value)/1048576) as 'Autoload(MB)' FROM $(wp config get table_prefix)options WHERE autoload='yes' ;"

Less than 600KB is good for most servers, but higher-performance servers such as DreamPress may be able to handle larger amounts of autoload data.

If your autoload size is larger than expected, run the following command:

[server]$ wp db query "SELECT option_name, LENGTH(option_value)/1048576 AS "Size" FROM $(wp config get table_prefix)options WHERE autoload='yes' ORDER BY "Size" DESC LIMIT 25 ;"

This will return the 25 largest rows in an options table that are being autoloaded. From here, you will need to examine the rows and determine what data is actually needed. Autoload data could be related to your theme or plugins, and you will need to research entries you don't recognize before deleting them. If you do find rows that can be safely removed, use the following command to do so:

[server]$ wp db query "DELETE FROM $(wp config get table_prefix)options WHERE option_name LIKE '%example_%' ;"
  

Remember to replace %example_% with the prefix of the row you want to delete.

Once a row is are removed, run wp db optimize again and test your site to ensure there are no adverse effects.

Indexing

Indexing helps the database server quickly find and return queries. You can check to see if your options table already has an index using the following command:

[server]$ wp db query "SHOW INDEX FROM $(wp config get table_prefix)options ;"

Generally, you should add an index when your database is between 60% and 80% non-autoloaded data. You can compare these values using:

[server]$ wp db query "SELECT COUNT(CASE WHEN autoload = 'yes' THEN 1 END) FROM $(wp config get table_prefix)options ;" ; wp db query "SELECT COUNT(CASE WHEN autoload = 'no' THEN 1 END) FROM $(wp config get table_prefix)options ;"

You can add an index to your options table using the following command:

[server]$ wp db query "CREATE INDEX autoload ON $(wp db config get table_prefix)options(autoload, option_name) ;"

If you need to delete an index, use the following command:

[server]$ wp db query "DROP INDEX autoload ON $(wp db config get table_prefix)options ;"

The postmeta table

The postmeta table contains miscellaneous metadata tied to a post on your site, such as that added by plugins and themes, or metadata for media library content. If your postmeta table is larger than your post table, more optimization may be needed. You will need to use two commands to search through the postmeta table, as the mate_key column uses a leading _ for hidden settings and no leading _ for user-accessible settings.

Run the following commands:

[server]$ wp db query "SELECT SUBSTRING_INDEX(meta_key, '_', 1) AS "Meta",(SUM(LENGTH(meta_id)+LENGTH(post_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS "Size", COUNT(*) AS "Count" FROM $(wp config get table_prefix)postmeta WHERE meta_key NOT LIKE '\_%' GROUP BY "Meta" ORDER BY "Size" DESC ;"
[server]$ wp db query "SELECT SUBSTRING_INDEX(meta_key, '_', 2) AS "Meta",(SUM(LENGTH(meta_id)+LENGTH(post_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS "Size", COUNT(*) AS "Count" FROM $(wp config get table_prefix)postmeta WHERE meta_key LIKE '\_%' GROUP BY "Meta" ORDER BY "Size" DESC ;"

The first command will list all user-accessible settings, sorted in descending order by row size. The second does the same, but for hidden settings.

Examine the results to determine what, if anything, can be removed. The following two commands may be useful:

[server]$ wp db query "SELECT MAX(t1.post_id) AS "post_id", MAX(t2.post_date) AS "Date", SUBSTRING_INDEX(t1.meta_key, '_', 1) AS "Meta", (SUM(LENGTH(meta_id)+LENGTH(post_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS "Size", COUNT(*) AS "Count" FROM $(wp config get table_prefix)postmeta AS t1 JOIN $(wp config get table_prefix)posts AS t2 ON t1.post_id = t2.ID WHERE meta_key NOT LIKE '\_%' GROUP BY "Meta" ORDER BY "Date" DESC ;"
[server]$ wp db query "SELECT MAX(t1.post_id) AS "post_id", MAX(t2.post_date) AS "Date", SUBSTRING_INDEX(t1.meta_key, '_', 2) AS "Meta", (SUM(LENGTH(meta_id)+LENGTH(post_id)+LENGTH(meta_key)+LENGTH(meta_value)))/1048567 AS "Size", COUNT(*) AS "Count" FROM $(wp config get table_prefix)postmeta AS t1 JOIN $(wp config get table_prefix)posts AS t2 ON t1.post_id = t2.ID WHERE meta_key LIKE '\_%' GROUP BY "Meta" ORDER BY "Date" DESC ;"

These will print two tables (one with a leading _ and one without) which display the meta_key string, the size it takes up in the database, the number of rows there are containing it and importantly, the date and post_id of the last time it was used. The meta_key value entries can be used to identify rows for plugins or themes that are no longer in use.

If any unnecessary rows are found, run the following command to delete them:

[server]$ wp db query "DELETE FROM $(wp config get table_prefix)options WHERE option_name LIKE '%example_%' ;"

Remember to replace %example_% with the prefix of the row you want to delete.

Additionally, there may be metadata in your postmeta table that is no longer associated with a post. Run the following command to find and remove metadata for deleted posts:

[server]$ wp db query "SELECT * FROM $(wp config get table_prefix)postmeta pm LEFT JOIN $(wp config get table_prefix)posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL ; DELETE pm FROM $(wp config get table_prefix)postmeta pm LEFT JOIN $(wp config get table_prefix)posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL ;"

The commentmeta table

The commentmeta table can become cluttered over time with metadata for deleted comments, such as those removed by a spam comment detection plugin.

To list the rows being deleted before removing them completely, use the following command:

[server]$ wp db query "SELECT * FROM $(wp config get table_prefix)commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM $(wp config get table_prefix)comments) ; DELETE FROM $(wp config get table_prefix)commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM $(wp config get table_prefix)comments) ;"

Post revisions

WordPress post revisions are historical or autosaved versions of your site's posts. WordPress limits the revision count to 10 by default, but these can as your site's post count grows. Use the commands below to clean up revisions in your site's database.

Run the following command to get a count of the total revisions on your site:

[server]$ wp post list --post_type='revision' --format=ids | wc -w

Run the following command to delete any existing revisions:

[server]$ wp post delete $(wp post list --post_type='revision' --format=ids) --force --quiet

Add the following directive to your wp-config.php file to limit the maximum number of revisions:

define('WP_POST_REVISIONS', 1);

Make sure any additions to your wp-config.php are done above the line that reads /* That's all, stop editing! Happy blogging. */.

See also

Did this article answer your questions?

Article last updated PST.

Still not finding what you're looking for?