A site with a few years behind it carries a database full of things nobody reads: a revision of every single save, spam comments from 2021, options from plugins deleted long ago. Clearing that out works no miracles, but it gives a real gain and frees space.
|
This deletes real data and there is no undo. Take two copies before the first command: one from the panel (how to restore your data) and an export of the database from phpMyAdmin itself, on the Export tab. Keep the export off the server.
|
What fills a database up
| What it is |
Why it piles up |
| Post revisions |
Every time you save a page, WordPress keeps the whole previous version. A page edited fifty times is fifty copies of it in the database. |
| Auto-drafts and the bin |
What was never published, and what went to the bin and never left. |
| Spam comments |
Marked as spam but not deleted. On an old site they run into thousands. |
| Expired transients |
Data cached by plugins so they do not repeat work. They should disappear on their own and often do not. |
| Leftovers from deleted plugins |
Options, whole tables and metadata that stay behind after deleting the plugin from the dashboard. |
| Orphan metadata |
Rows attached to posts and users that no longer exist. |
The risk-free clean: optimising the tables
This deletes nothing: it tidies. After a lot of writing and deleting, tables carry dead space inside them, and optimising rebuilds them.
| 1 |
In cPanel open phpMyAdmin and pick the site’s database from the left-hand list. If you do not know which it is, find it first.
|
|
| 2 |
Below the table list, click Check all.
|
|
| 3 |
In the box beside it, choose Optimize table and confirm.
|
|
|
If a note says the table does not support optimize, all is well. Modern MySQL tables answer that and rebuild anyway. It is not an error and there is nothing else to do.
|
The real clean: deleting what is left over
These commands run on phpMyAdmin’s SQL tab, one at a time. Swap wp_ for your own table prefix, which you can see in the left-hand list.
| What it deletes |
The command |
| The revisions |
DELETE FROM wp_posts WHERE post_type = 'revision'; |
| The auto-drafts |
DELETE FROM wp_posts WHERE post_status = 'auto-draft'; |
| Spam and binned comments |
DELETE FROM wp_comments WHERE comment_approved IN ('spam','trash'); |
| The transients |
DELETE FROM wp_options WHERE option_name LIKE '%_transient_%'; |
| Orphan metadata |
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts p ON p.ID = pm.post_id WHERE p.ID IS NULL; |
|
Confirm the prefix before every command. If the database serves two sites with different prefixes, running one of these with the wrong prefix deletes the other site’s content. And if you are unsure what a command does, start by swapping DELETE for SELECT * to see how many rows it catches first.
|
What weighs on every single page
There is one thing in the database that is read on every request, and so counts for more than all the rest put together: the options marked to autoload. A badly written plugin stores enormous things in there, and they go on being read a thousand times a day even after the plugin is gone.
To see the biggest, run this on the SQL tab:
SELECT option_name, LENGTH(option_value) AS size FROM wp_options WHERE autoload = 'yes' ORDER BY size DESC LIMIT 20;
If one of the top rows carries the name of a plugin you no longer use, it is rubbish and can go. If you do not recognise the name, leave it and ask whoever built the site: an option deleted on a hunch can break a whole feature.
Stopping it filling up again
Cleaning once fixes today. These two lines in wp-config.php, above the line telling you to stop editing, fix next year:
| 1 |
define( 'WP_POST_REVISIONS', 5 ); keeps the last five revisions of each text instead of all of them.
|
|
| 2 |
define( 'EMPTY_TRASH_DAYS', 7 ); empties the bin after a week instead of thirty days.
|
|
|
And delete the plugins you do not use; deactivating is not enough. A deactivated plugin still takes database space with its options, and it is still an unpatched door: protecting the WordPress login.
|
When this is not the answer
Worth saying, to save you the work: cleaning the database rarely turns a slow site into a fast one. The changes with the biggest payback are other ones, and they are listed in order in the site is slow: what actually makes a difference. If what you want is to free files and space on the account, the route is the limits nobody advertises.
|
Database grown and you cannot see why? Send us the domain.
Open a request
|
Angola
Brasil
Kenya
Moçambique
Nigeria
Portugal
South Africa
United Kingdom
United States
International
