One day you notice the account is fuller than it should be, and that a good part of it is the database. The right question at that point is not «how do I clean it?». It is what is in there taking up the room. This article is about measuring and deciding, which is the part people skip.
The cleaning itself, with the commands already written out, is in cleaning and optimising the database in phpMyAdmin. Do that after you know what you are deleting, not before.
Where to see the size
| 1 |
In cPanel, in the MySQL databases area. Nothing to install and nothing to run: the list of your account’s databases carries, beside each name, the room that database takes. It is the shortest route, and it is this number that decides whether digging further is worth it: if the database is a few megabytes, it is not the database, it is the files.
|
|
| 2 |
In phpMyAdmin, table by table. Pick the database in the left-hand list and the page that opens shows the tables with a size column. Sort by it and look only at the top three: on a normal database, three tables explain nearly all of it. If you do not know which database the site uses, find that out first.
|
|
| 3 |
With a query, if you want the whole picture at once. On phpMyAdmin’s SQL tab, swap account_db for your database’s full name, prefix included, and run the command below.
|
|
SELECT table_name, ROUND((data_length + index_length)/1024/1024, 1) AS mb, ROUND(index_length/1024/1024, 1) AS mb_indexes, ROUND(data_free/1024/1024, 1) AS mb_dead, table_rows FROM information_schema.tables WHERE table_schema = 'account_db' ORDER BY data_length + index_length DESC;
| The column |
What it tells you |
| mb |
The table’s total weight, data and indexes added together. The list is sorted by this one. |
| mb_indexes |
How much of that weight is indexes. Large indexes are not waste: they are what makes queries fast. Do not drop them to save room. |
| mb_dead |
Room that was once in use and is now free inside the table’s own file. If this number is high, somebody has deleted a lot and the table still needs rebuilding before the space goes back to the disk. |
| table_rows |
An estimate of the row count, not a count. It is fine for comparing tables against each other; for the exact figure on one, count it with SELECT COUNT(*). |
What usually grows, and how to confirm it
On a WordPress site the fat tables are nearly always the same four things. None of them has to be guessed at: each one is confirmed with a count on the SQL tab. Swap wp_ for your own table prefix.
| What it is |
How to confirm it is that |
| Post and page revisions |
Every save keeps the whole previous version. Run SELECT post_type, COUNT(*) FROM wp_posts GROUP BY post_type; . If the revisions line dwarfs the posts and pages lines, you have found it. |
| Comments marked as spam |
Marked, but never deleted. Run SELECT comment_approved, COUNT(*) FROM wp_comments GROUP BY comment_approved; . On an old site the spam line runs into thousands. |
| Tables from plugins you uninstalled |
Look at the names of the biggest tables. The WordPress ones are few and always the same; anything else belongs to a plugin. If the plugin is no longer installed, the table was left behind and nothing reads it. |
| Event logs |
Tables with log in the name, left by security, order or form plugins: they write a row per event and never delete anything. Sort the table by its oldest date; if it holds rows from years back, that settles it. |
There is a fifth thing that does not show up in the size but weighs on every single visit: the options marked to autoload. They are read on every request, including the ones left over from a plugin that is long gone. The query that lists them by size is in the cleaning article.
Deciding: what goes, what goes after a check, and what stays
| 1 |
Goes, no question: old revisions, auto-drafts, whatever has sat in the bin for months, comments already marked as spam, and event logs with years on them.
|
|
| 2 |
Goes after a check: plugin tables. Confirm first that the plugin really is uninstalled and not merely deactivated. A deactivated plugin gets switched back on one day and goes looking for its tables.
|
|
| 3 |
Stays: anything you cannot identify. A table deleted on a hunch can stop a whole feature working, and the only way back is the copy. When in doubt, ask whoever built the site, or ask us.
|
|
Two things worth knowing before you start
A big database is rarely why a site is slow. If what bothers you is speed rather than space, cleaning the database will give you very little: the changes that pay are listed in order in the site is slow: what actually makes a difference.
And if what you are short of is room on the account, note that the ceiling that usually bites first is not weight: it is the number of files. It is explained in the limits nobody advertises.
|
Measure again after cleaning, and do not panic if the number does not drop straight away. Deleting rows does not shrink the table’s file at once: the space stays inside it, marked as free, and only returns to the disk when the table is rebuilt. That is the dead column in the query above, and tidying it is exactly what optimising does.
|
|
Measured it and cannot place the biggest table? Send us the domain and the table name.
Open a support ticket
|
RECOMMENDED PRODUCT Web hosting with cPanel Domain and SSL included, daily backups and the panel you already know. from $6.60/mo (3-year plan, with coupon) See plans |
Angola
Brasil
Kenya
Moçambique
Nigeria
Portugal
South Africa
United Kingdom
United States
International
