How to check how big your database is

The quickest way to check a database's size is usually your control panel or phpMyAdmin, both of which display it without needing to write anything. A short SQL query gives you the same figure and, more usefully, breaks it down table by table so you can see exactly what is taking up the space.

Checking it in phpMyAdmin

Select the database from the left-hand list and open its Structure tab. Alongside every table you will see its individual size, and phpMyAdmin totals these at the bottom of the page to give you the size of the whole database. This is normally the fastest route if you just want a rough figure and do not need to run anything yourself.

Checking it with a query

For the total size of one specific database, run the following in phpMyAdmin's SQL tab or from any connected client, replacing the database name with your own:

SELECT table_schema AS "Database",
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = 'database_name'
GROUP BY table_schema;

This adds together the space used by the actual data and the space used by indexes, across every table in the named database, and converts the result from bytes into megabytes.

Finding which tables are the largest

If a database is bigger than expected, it is usually one or two tables driving most of it, and the total figure above will not tell you which ones. This breaks it down per table instead:

SELECT table_name AS "Table",
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = 'database_name'
ORDER BY (data_length + index_length) DESC;

The largest tables appear first. On most sites this turns out to be logging tables, session data, revision history, or an ecommerce store's order and product tables, all of which tend to grow continuously unless something is actively trimming them.

Why this is worth checking occasionally

Hosting plans commonly measure database storage against your overall account allowance, and a database that has grown far larger than the site itself would suggest is worth investigating before it becomes a problem rather than after. Checking your account resource usage covers where database size fits into your plan's wider limits, and how many databases you can create if storage is not the only allowance you are watching.

If a database is larger than it should be

Old log tables, abandoned plugin data, and years of accumulated post revisions are common reasons a database ends up far bigger than the content it actually holds. Optimising a MySQL database covers cleaning this up safely, and taking a fresh export before making any changes is worth doing regardless of how confident you are about what you are removing.