check mysql database size and table size

check the sizes of all of your databases, at the mysql> prompt type the following command:

SELECT table_schema "DB Name",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables 
GROUP BY table_schema;

To check the sizes of all of the tables in a specific database, at the mysql> prompt, type the following command. Replace database_name with the name of the database that you want to check:

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 = "spro" ORDER BY (data_length + index_length) DESC;

mysql -u root -p

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

mysql> SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+--------------+
| Database | Size (MB) |
+--------------------+--------------+
| information_schema | 0.00000000 |
| mysql | 4.04687500 |
| performance_schema | 0.00000000 |
| spro | 686.14062500 |
| sys | 0.01562500 |
+--------------------+--------------+

mysql> SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "spro" ORDER BY (data_length + index_length) DESC;
+------------------------------------+-----------+
| Table | Size (MB) |
+------------------------------------+-----------+
| 2xpwp_posts | 287.20 |
| 2xpwp_postmeta | 200.94 |
| 2xpwp_redirection_404 | 70.67 |
| 2xpwp_options | 24.47 |
| 2xpwp_actionscheduler_actions | 17.17 |
| 2xpwp_yoast_seo_links | 12.39 |
| 2xpwp_wsal_metadata | 10.55 |
| 2xpwp_actionscheduler_logs | 10.05 |
| 2xpwp_yoast_indexable | 6.00 |
| 2xpwp_wpr_rocket_cache | 3.36 |
| 2xpwp_term_relationships | 2.89 |
| 2xpwp_13_posts | 1.56 |