Databases count toward your disk quota, and a bloated database also makes a site slower and backups larger. Often a single table, such as a plugin's log or a sessions table, has grown to gigabytes. This guide shows how to find it and clean it safely.
Before you start
- Back up first. Export the database in phpMyAdmin or rely on yesterday's JetBackup copy. Deleted rows cannot be undone otherwise.
- Know which app uses which database (for WordPress,
DB_NAMEinwp-config.php).
Find the largest databases
Open cPanel → Databases → Manage My Databases. The Size column shows each database. Disk Usage shows the total for all databases.
Find the largest tables
- Open phpMyAdmin and click the database.
- Click the Size column header in the table list to sort.
- Or run this in the SQL tab (replace the database name):
SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb, table_rows FROM information_schema.tables WHERE table_schema = 'cpuser_wp' ORDER BY size_mb DESC LIMIT 15;
Usual suspects and safe cleanups
| Table / data | What it is | Cleanup |
|---|---|---|
| Plugin log tables (security, email, redirect logs) | Activity logs that grow forever | Reduce log retention in the plugin, or empty via its settings |
wp_options with many _transient_ rows | Temporary cached data | Delete expired transients with a cleanup plugin or wp transient delete --expired |
wp_posts revisions | Old versions of every edit | wp post delete $(wp post list --post_type=revision --format=ids) --force, and limit with define('WP_POST_REVISIONS', 5); |
| Spam and trashed comments | Unwanted comments | Empty spam and trash in the CMS |
| Session or cache tables (shops, Drupal, Matomo) | Temporary data | Use the app's own purge function |
| Old WooCommerce action scheduler rows | Completed background jobs | Clear completed actions in WooCommerce → Status → Scheduled Actions |
WP-CLI commands run in cPanel Terminal from the WordPress folder.
Warning: Never empty a table just because it is big. Orders, users, posts and settings live in large tables too. If you are not sure what a table is, look up its name or ask support.
Reclaim the space: optimise tables
Deleting rows does not always shrink the files on disk. After cleaning:
- In phpMyAdmin, tick the tables you cleaned (or Check all).
- Choose With selected: Optimize table.
InnoDB rebuilds the tables and releases unused space. Large tables can take a while; do it at a quiet time. The KB guide optimise and repair tables covers this in more detail.
Prevent regrowth
- Set log retention in plugins to 30 days or less.
- Limit revisions and enable automatic spam deletion.
- Remove plugins you no longer use; many leave tables behind, which you can drop after checking.
- Schedule a monthly check of the table sizes.