Optimising Your Database
Over time, databases can become bloated and slow due to fragmented tables, unnecessary data, and inefficient queries. Regular optimisation keeps your website fast and your database healthy.
Why Optimise?
When rows are deleted or updated in MySQL, the space they occupied is not immediately reclaimed. This creates fragmentation that wastes disk space and slows down queries. Optimisation defragments tables and improves performance.
Optimising Tables in phpMyAdmin
- Open phpMyAdmin and select your database.
- Scroll to the bottom of the table list and click Check All to select all tables.
- From the With selected dropdown menu, choose Optimize table.
- phpMyAdmin will process each table and report the results.
Cleaning Up WordPress Databases
If you use WordPress, these actions can significantly reduce database bloat:
- Delete post revisions – WordPress saves every revision of every post. Run this SQL query to remove them:
DELETE FROM wp_posts WHERE post_type = 'revision'; - Clean transients – Expired transient data builds up over time. Delete them with:
DELETE FROM wp_options WHERE option_name LIKE '_transient_%'; - Remove spam comments – Delete all spam comments:
DELETE FROM wp_comments WHERE comment_approved = 'spam'; - Clean up auto-drafts – Remove unused auto-saved drafts:
DELETE FROM wp_posts WHERE post_status = 'auto-draft';
Using Optimisation Plugins
For WordPress users who prefer not to run SQL queries manually, plugins like WP-Optimize or Advanced Database Cleaner provide a user-friendly interface for cleaning and optimising your database. These can also be scheduled to run automatically.
Regular Maintenance Schedule
We recommend optimising your database at least once per month for active websites. Sites with high traffic, frequent content updates, or e-commerce activity may benefit from weekly optimisation. Always create a backup before running optimisation queries.
Monitoring Performance
If your website feels slow, check the slow query log in cPanel to identify database queries that take too long. Slow queries often indicate missing indexes or poorly structured queries that can be improved.