Optimizing WordPress databases

Overview

This article describes several methods of optimizing a WordPress site's database.

Background

Online tools cannot access the WordPress database, so it's possible to have a high score on these tools while having poor database performance. The commands in this article help to optimize your database to ensure it's functioning as efficiently as possible.

Optimizing database tables

These instructions update the following tables:

• options table
• postmeta table
• commentmeta table

To run these wp-cli commands, you must log into your server via SSH and navigate to your WordPress site's directory. 

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.

Delete and optimize transients

Delete any transients in the WordPress database:

[server]$ wp transient delete --all

Optimize the remaining tables in the database:

[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 WordPress dashboard performance and may affect the public-facing site as well.

View autoload data

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.

View the largest autoload rows

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.

Remove rows

Remove rows that can have confirmed can be safely removed.

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

Replace example_ with the prefix of the row you want to delete. Also, make sure to keep the % symbols at the beginning and end as they act as wildcard queries.

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.

Check indexes

Check to see if your options table already has an index:

[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.

Compare indexes

Compare these values:

[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 ;"

Index a table

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

Delete an index

Run the following to delete an index:

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

The postmeta table

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.

Command 1 — List all user-accessible settings

This command creates a list sorted in descending order by row size.

[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 ;"

Command 2 — List hidden settings

This command does the same for hidden settings.

[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 ;"

Examine results

Examine the results to determine what, if anything, can be removed. 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.

[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 ;"

Delete rows

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_%' ;"

Delete metadata

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.

List rows

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.

Count total revisions

Get a count of the total revisions on your site:

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

Delete revisions

Delete any existing revisions:

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

Limit revisions

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

• Optimize a database
• General WordPress optimization techniques
• MySQL overview
• phpMyAdmin overview