This article shows you different ways of optimising your WordPress database, making your site run smoother and improving loading time.
Note: Be very 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 the premium plugin WP Rocket.
Delete transients
WordPress stores cache data for plugins in the wp_options table called transients that improve the performance of your website. You can compare them to cookies in your browser.
Transients have an expiration date and are automatically deleted when they run out. 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's a good idea.
- Open the database in phpMyAdmin.
- Click SQL in the menu at the top of the screen.
- 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%'
- 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:
- Open the database in phpMyAdmin.
- Click SQL in the menu at the top of the screen.
- 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';
- 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:
- Open the database in phpMyAdmin.
- Click SQL in the menu at the top of the screen.
- 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
- 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.
- Open the database in phpMyAdmin.
- Click SQL in the menu at the top of the screen.
- 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%'
- 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 from WordPress admin.
- In the menu to the left click WooCommerce > Status > Tools.
- 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 from your WordPress admin. In that case, you need to do it from the database in phpMyAdmin.
- Open the database in phpMyAdmin.
- Click SQL in the menu at the top of the screen.
- 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_%'
- Click Go to confirm your action.
Related articles: