How to optimise your WordPress database without any plug-ins

WordPress, as a database-driven CMS, relies heavily on its database to store essential data like posts, comments, settings, customer orders, and user information.

Over time, this database can become cluttered with unnecessary data, which not only increases its size but can also slow down your website. 

Regularly optimising your database offers several benefits: 

• Reduced database size, saving server resources. 

• Faster query execution, improving performance. 

• Enhanced website speed, resulting in a better user experience. 

• Simplified database management. 

In this guide, we’ll explore common types of unnecessary data in WordPress databases and how to clean them up, without relying on plugins. 

Familiarise Yourself with WordPress Databases 

Before diving in, take time to understand the structure of WordPress database tables.

Familiarity with tables like wp_posts, wp_options, and others will help you confidently identify and remove unwanted data. 

Using phpMyAdmin for Database Management 

You can manage your WordPress database via phpMyAdmin.

Access it in My20i > Manage > Web Tools > phpMyAdmin.

Once there, sorting tables by size can help identify the largest ones for optimisation. 

Backup First! 

Always back up your database before making any changes. You can: 

• Export it directly using phpMyAdmin’s Export tool. 

• Use the Backup Restore tool in My20i for a complete backup. 

Cleaning Up Your WordPress Database 

Remove Post Revisions 

WordPress creates a new revision every time you update a post. These revisions can accumulate over time. To clean them up: 

• Use phpMyAdmin to filter rows in the wp_posts table with the keyword revision, then delete them. 

• Alternatively, execute this SQL query: 

DELETE FROM wp_posts WHERE post_type = 'revision'; 

To prevent excess revisions in the future, add this to your wp-config.php file: 

define('WP_POST_REVISIONS', 5); 

Delete Auto-Drafts 

Auto-drafts are temporary saves of posts or pages. Once published, they remain in the database unnecessarily. Use phpMyAdmin to search for auto-draft and delete the rows, or run: 

DELETE FROM wp_posts WHERE post_status = 'auto-draft'; 

Clear Trash Data 

WordPress sends deleted items to the trash instead of permanently deleting them.

You can speed up trash clearance by adding this to wp-config.php: 

define('EMPTY_TRASH_DAYS', 7); 

Note: Replace 7 with your preferred number of days. 

Remove Spam Comments 

Spam and unapproved comments are stored in the wp_comments table.

Delete them manually via phpMyAdmin or write a query to bulk-remove spam. 

Clean Up Old Plugin and Theme Data 

Unused plugins and themes can leave behind orphaned data. Search tables like wp_options, wp_postmeta, and wp_commentmeta for prefixes associated with the plugin or theme, e.g., WooCommerce uses wp_wc_. 

Check for Plugin Cron Jobs 

Some plugins leave behind cron jobs after deactivation. Use phpMyAdmin to review the wp_options table and delete outdated cron jobs. 

Optimise Database Tables 

The Optimize Table command in phpMyAdmin can be useful when reducing table sizes. 

The command essentially defragments data, rebuilds indexes and reclaims unused space. 

It’s recommended to use the tool as part of routine database optimisation, after deleting large amount data and if your website experiences slow queries. 

Boost Performance with Redis Caching 

Employing Redis a caching layer will benefit your wordpress website. Redis stores results of MySQL queries in memory and Redis will serve the results instantly.

Because the queries are already cached, this will free MySQL/MariaDB to handle other tasks more effectively.

Our WordPress optimised Managed Cloud hosting comes with Redis pre-installed. 

Final thoughts 

WordPress is a robust platform, but as a database-driven CMS, it requires regular maintenance.

By removing clutter like spam comments, post revisions, and leftover plugin data, you’ll keep your database lean and efficient.

This proactive approach ensures faster database queries, better server resource utilisation, and an overall improvement in website performance.

Take advantage of tools like phpMyAdmin, Redis caching, and 20i’s optimised hosting to maintain a healthy, high-performing WordPress site.