View Sizes of MySQL Databases & Tables over SSH

by Geoff Jackson · August 28, 2015

Sometimes, your typical database GUI (such as phpMyAdmin) won’t be usable for managing really big databases and you’ll have to work directly on the command line (SSH) for this.

After connecting to your server, you need to log into mysql with something like the following (this could vary depending on your server OS and set up):-

mysql -u username -p

1	mysql -u username -p

You will then be prompted to enter your MySQL password which you must do.

To view a list of databases and their sizes, use the following:-

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

SELECT table_schema AS "Database",

ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"

FROM information_schema.TABLES

GROUP BY table_schema;

To view a list of tables in a database and sort them by size (highest first), use the following:-

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;

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;

Make sure you replace DATABASE_NAME with the name of the database you want to investigate.

Tags: mysql ssh

Geoff Jackson

Geoff Jackson (more commonly known by his online pseudonym zigojacko) is the founder of Design Haven and The Clubnet Group which hosts numerous agencies including the most widely recognised of them, Clubnet Digital, a full service digital marketing and creative design/development agency. He also has a personal blog but it doesn't get updated as much as he'd like.