Check MySQL database table disk usage

This guide advises how to check the disk usage of your MySQL database tables

To complete this guide, please ensure you have SSH Access set up, Serversaurus relies on key based authentication to login via SSH/SFTP, so if you haven't already, generate your SSH key pair and configure it in cPanel before proceeding with the next steps.

Let's begin!

1. SSH to your hosting server
2. Login to MySQL using the below command, enter your password when prompted:

   mysql -u mysql_username -p;

   Update mysql_username with your database user.

3. Update the following command with your database name and then paste into your MySQL terminal window:
   
   SELECT TABLE_NAME AS `Table`,
   ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
   FROM
   information_schema.TABLES
   WHERE
   TABLE_SCHEMA = 'YOUR_DATABASE_NAME_GOES_HERE'
   ORDER BY
   (DATA_LENGTH + INDEX_LENGTH)DESC;
4. To refine the results, query the database to return only the top 10 tables consuming the most disk usage:SELECT TABLE_NAME AS `Table`,
   ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
   FROM
   information_schema.TABLES
   WHERE
   TABLE_SCHEMA = 'YOUR_DATABASE_NAME_GOES_HERE'
   ORDER BY
   (DATA_LENGTH + INDEX_LENGTH)
   DESC LIMIT 10;You can adjust how many results are returned by modifying the number next to LIMIT.
5. After querying the database, the results will display the database tables descending in size: