MySQL queries to show the size of databases and tables reported by information_schema

mysql_db_and_table_size_queries.sql

-- Check the total size of each database
SELECT
  tables.TABLE_SCHEMA AS database_name,
  ROUND(SUM(tables.DATA_LENGTH + tables.INDEX_LENGTH) / POWER(2, 30), 3) AS database_size_in_gb
FROM information_schema.TABLES AS tables
GROUP BY tables.TABLE_SCHEMA
ORDER BY database_size_in_gb DESC;

-- List all tables larger than 1 GB
SELECT
  CONCAT(tables.TABLE_SCHEMA, '.', tables.TABLE_NAME) AS database_name_and_table_name,
  ROUND(SUM(tables.DATA_LENGTH + tables.INDEX_LENGTH) / POWER(2, 30), 3) AS table_size_in_gb
FROM information_schema.TABLES AS tables
GROUP BY database_name_and_table_name
HAVING table_size_in_gb > 1
ORDER BY table_size_in_gb DESC;