If you’re running a WordPress website, optimizing your database is crucial for improving the performance and speed of your site and keep database backups small. URLsLab plugin can help you to clean up your WordPress database, ensuring that it runs efficiently and smoothly.
Over time, your WordPress database can become cluttered with unnecessary data such as revisions, spam comments, trashed posts, transient options, and more. This can slow down and make expensive backups and affect overall performance. A database optimization module in URLsLab plugin helps you periodically (based on your schedule) remove this unnecessary data.
URLsLab plugin can delete following data from your WordPress database to keep it healthy and efficient.
Database cleanup tasks are executed on background by cron tasks. It is not guaranteed when the task will be executed, as there are multiple background tasks and each of them needs some time slots to be executed. Once the task is finished, next execution of cleanup is postponed based on your schedule interval in Settings. If you need to delete data from db tables manually, use the top menu in the administration toolbar. Some actions called from toolbar will delete just few thousands rows, so if you see the table is still not cleaned completely, you need to call the action multiple times.
If you are developer and you don’t want to use any external WordPress plugin to cleanup your database, here are few SQL commands you could use to cleanup some of your WordPress database tables. URLsLab does the same SQL commands on background by cron task.
Hint: It is good practice to delete just few thousands rows per one SQL call. If you don’t use limit in your delete SQL, you can overload your DB server in case there are millions of rows.
e.g. to delete post revisions modified before 2023, execute following SQL:
DELETE FROM posts WHERE post_type='revision' AND post_modified < '2023-01-01' LIMIT 5000
DELETE FROM posts WHERE post_status = 'auto-draft' AND post_modified < '2023-01-01' LIMIT 5000
DELETE FROM posts WHERE post_status = 'trash' AND post_modified < '2023-01-01' LIMIT 5000
Transient options are used by plugins to store temporary or time limited data. Some plugins can stop to work if you delete their data, or they will need to recompute them again.
DELETE FROM options WHERE option_name LIKE '%_transient_%' LIMIT 5000
This SQL can be quite expensive on your DB server, because it lists all post ids as sub-select and checks if id is not in the list. Same can be achieved by left join if your DB is overpowered.
DELETE FROM term_relationships WHERE term_taxonomy_id=1 AND object_id NOT IN (SELECT id FROM posts) LIMIT 5000
If the SQL is slow on your server, it is possible to rewrite it with LEFT join.
DELETE FROM commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM comments) LIMIT 5000
URLsLab is not the only plugin for WordPress database cleanup or optimization. Here are examples of other similar plugins you can use to keep your WordPress database small.
This popular plugin provides a simple and intuitive interface for optimizing your WordPress database. It allows you to clean up your database by removing unnecessary data, optimizing tables, and performing other maintenance tasks. WP-Optimize also offers scheduling options, allowing you to automatically optimize your database at regular intervals.
WP-Sweep is another powerful database optimization plugin that helps you clean up and optimize your WordPress database. It offers various sweeping options, including removing revisions, cleaning up spam comments, deleting unused tags, and optimizing your database tables. WP-Sweep also provides detailed reports on the optimizations performed.
This plugin is designed to thoroughly clean and optimize your WordPress database. It offers a range of cleaning options, including removing unused tables, optimizing the database, and cleaning up transient options. Advanced Database Cleaner also provides a backup and restore feature, allowing you to safely perform optimizations without the risk of data loss.
WP-DBManager is a comprehensive database management plugin that allows you to optimize, repair, and backup your WordPress database. It offers features such as automatic scheduling of database optimization, repairing corrupted tables, and performing backups. WP-DBManager also allows you to run many other maintenance tasks on your database.
WP Reset plugin focus mainly on resetting all WordPress values to default installation state, but additionally can delete transients, delete uploads, delete plugins you don’t need, reset theme options, delete themes, etc.
If you know about any other database optimization plugin, just let us know, we will extend this list to help other WordPress users to keep their database small.