Optimizing WordPress databases

WordPress sites are composed of two main components: the website files and the database. The webserver files contain images, the plugins and theme files, and the WordPress core files. The database contains 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 while having poor database performance. The following article describes several methods of optimizing a WordPress site's database.

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

To delete and optimize:

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 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' or autoload='on';"

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' or autoload='on' ORDER BY "Size" DESC LIMIT 25;"

This command returns 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 can 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_%' ;"

Once a row is 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 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 config get table_prefix)options ;"

The postmeta table contains miscellaneous metadata tied to a post on your site, such as those 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 lists 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 commands 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_postmeta WHERE meta_key LIKE '%example_%' ;"

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