# Größe einer DB, Tabelle, aller DBs herausfinden Ersetze 'YOUR\_DATABASE\_NAME' durch die gewünschte Datenbank. Größe aller Tabellen einer Datenbank: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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 wie viel Platz gewonnen werden könnte: ```sql 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; ``` wo kann am meisten Platz gewonnen werden (sortiert nach free\_space): ```mysql 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; ```