Größe einer DB, Tabelle, aller DBs herausfinden

Ersetze 'YOUR_DATABASE_NAME' durch die gewünschte Datenbank.

Größe aller Tabellen einer Datenbank:

SELECT TABLE_SCHEMA AS 'Database_name', TABLE_NAME AS 'Table_Name',CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," Mb") AS Size FROM INFORMATION_SCHEMA.TABLES;

Größe bestimmter Tabellen:

SELECT TABLE_SCHEMA AS 'Database_name', TABLE_NAME AS 'Table_Name',CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'YOUR_DATABASE_NAME';

Größe einer bestimmten Datenbank:

SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA =  'YOUR_DATABASE_NAME';

Größe aller Datenbanken:

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

Größe aller Datenbanken und wieviel Platz durch ein "OPTIMIZE TABLE" gewonnen werden kann:

SELECT table_schema "database name", round( sum( data_length + index_length ) / 1024 / 1024) "database size in MB", round( sum( data_free )/ 1024 / 1024) "free space in MB" FROM information_schema.TABLES GROUP BY table_schema;

Die 10 größten Tabellen anzeigen und wieviel Platz gewonnen werden könnte:

select table_schema as database_name,
       table_name,
       round( (data_length + index_length) / 1024 / 1024, 2) as total_size,
       round( (data_length) / 1024 / 1024, 2) as data_size,
       round( (index_length) / 1024 / 1024, 2) as index_size,
       round( (data_free) / 1024 / 1024) as free_space
from information_schema.tables
where table_schema not in ('information_schema', 'mysql', 'performance_schema' ,'sys')
      and table_type = 'BASE TABLE'
      -- and table_schema = '$i'
order by total_size desc
limit 10;

Die 10 Tabellen anzeigen wo am meisten Platz gewonnen werden könnte:

select table_schema as database_name,
       table_name,
       round( (data_length + index_length) / 1024 / 1024, 2) as total_size,
       round( (data_length) / 1024 / 1024, 2) as data_size,
       round( (index_length) / 1024 / 1024, 2) as index_size,
       round( (data_free) / 1024 / 1024) as free_space
from information_schema.tables
where table_schema not in ('information_schema', 'mysql', 'performance_schema' ,'sys')
      and table_type = 'BASE TABLE'
      -- and table_schema = '$i'
order by free_space desc
limit 10;