How to optimise the WordPress database

This article shows you different ways for optimising your WordPress database, which can make your site faster.

Note: Be careful when you work in phpMyAdmin, as it is not possible to undo changes. We recommend creating a backup before you make any changes.

• Recommended plugins to optimise the database
• Delete transients
• Delete post revisions
• Delete orphaned post meta
• Delete leftover data from uninstalled plugins
• Delete WooCommerce sessions

Note: When we mention the wp_options table in a query, or other tables in your database, you need to replace "wp" with the prefix used in your own WordPress installation. A prefix is the identifier that comes before the part "_options". Some examples are: "www_options", "wp_options" or "subdomain_options".

Recommended plugins to optimise the database

If you are unfamiliar with database queries, try one of these free open-source plugins listed below or check out our premium plugin WP Rocket.

• Plugins Garbage Collector
• WP-Optimize
• Advanced Database Cleaner
• WP-Sweep
• Media Cleaner

Did you know? 
Our premium caching plugin, WP Rocket, can significantly enhance your website's loading speed and SEO ranking. Additionally, it offers a range of automated features, including file optimisation, improved lazy loading for images, and much more.

Buy WP Rocket

Delete transients

WordPress stores cached plugin data in the wp_options table. This data is called 'transients' and it improve the performance of your website. You can compare them to cookies in your browser.

Transients have an expiration date on which they are automatically deleted. However, sometimes this goes wrong, and the data doesn't get deleted. Especially when you have a large website, this can add up over time and start cluttering your database, making your site run slower. That's why cleaning up and deleting transients is a good idea.

1. Open the database in phpMyAdmin.
2. Click SQL in the menu at the top of the screen.
3. Paste in the following query to remove all transients from this table, replacing wp_options with the name of the options table in your WordPress installation.

DELETE FROM `wp_options`
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%transient%'

4. Click Go to confirm your action.

Delete post revisions

Whenever you update a post or page on your website, the old content is saved as a revision in your database, allowing you to revert back to a previous version. Over time this can add up, especially if you make frequent changes. 

Use the query below to delete all saved revisions of your posts and pages:

1. Open the database in phpMyAdmin.
2. Click SQL in the menu at the top of the screen.
3. Paste in the following query to remove all transients from this table, replacing wp with the prefix of your own WordPress site. You need to replace wp in four different places.

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)
WHERE a.post_type = 'revision'
AND d.taxonomy != 'link_category';

4. Click Go to confirm your action.

Delete orphaned post metadata

Each post contains metadata that is stored in your database, such as the name of the author, the date it was published, etc. When you delete or change a post, the metadata is still there, called orphaned because it's no longer connected to a post and only clutters your database. 

Use the query below to remove the orphaned post metadata:

1. Open the database in phpMyAdmin.
2. Click SQL in the menu at the top of the screen.
3. Paste in the following query to remove all transients from this table, replacing wp with the prefix of your own WordPress site. You need to replace wp in four different places.

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

4. Click Go to confirm your action.

Delete leftover data from uninstalled plugins

When you uninstall plugins from the WordPress admin, some data is left behind in your database. To prevent your database from filling up, it's a good idea to remove this.

In the example below, we remove data from the Disqus plugin. To remove leftover data for other plugins, replace disqus in the query below with the name of another plugin listed in your wp-options table.

1. Open the database in phpMyAdmin.
2. Click SQL in the menu at the top of the screen.
3. Paste in the following query to remove all leftover data for the Disqus plugin from this table, replacing wp_options with the name of your options table.

DELETE FROM `wp_options`
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%disqus%'

4. Click Go to confirm your action.

Delete WooCommerce sessions

If you use WooCommerce and have a large wp_options table, this is likely because of sessions or transient data. You can delete this in WP Admin.

1. In the menu to the left click WooCommerce > Status > Tools.
2. Clear WooCommerce transients, Expired transients, Orphaned variations and Customer sessions. Note that customer sessions will also delete current and saved carts.

Delete WooCommerce sessions with a database query

If the WordPress database is too big, you can't delete sessions in WP Admin. In that case, you need to do it from the database in phpMyAdmin.

1. Open the database in phpMyAdmin.
2. Click SQL in the menu at the top of the screen.
3. Paste in the following query to remove all transients from this table, replacing wp_options with the name of the options table in your WordPress installation.

DELETE FROM wp_options
WHERE option_name LIKE '_wc_session_%' OR option_name LIKE '_wc_session_expires_%'

4. Click Go to confirm your action.

Related articles:

• How do I remove spam comments from WordPress?
• How to create a temporary guest user in WordPress