MySQL commands

mysql.sql

# Calculate the size of each database

## Order by name
SELECT 
  table_schema AS `Database`, 
  ROUND((SUM(data_length + index_length) / 1024 / 1024), 2) AS `Size in MB` 
FROM 
  information_schema.tables
GROUP BY 
  table_schema
ORDER BY table_schema ASC;

## Order by size (lagest first)
SELECT 
  table_schema AS `Database`, 
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Size in MB`
FROM 
  information_schema.tables
GROUP BY 
  table_schema
ORDER BY 
  SUM(data_length + index_length) DESC;



# Calculate the size of each table in a database

## Order by name
SELECT 
  table_name AS `Table`, 
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size in MB` 
FROM 
  information_schema.TABLES 
WHERE 
  table_schema = "DATABASE_NAME"
ORDER BY 
  table_name ASC;

## Order by size (lagest first)
SELECT 
  table_name AS `Table`, 
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size in MB` 
FROM 
  information_schema.TABLES 
WHERE 
  table_schema = "DATABASE_NAME"
ORDER BY (data_length + index_length) DESC;