Skip to content

Instantly share code, notes, and snippets.

@jeremycohensolal
Created August 2, 2017 15:17
Show Gist options
  • Save jeremycohensolal/608f2ffd0d701145e042fa678ba2c6a9 to your computer and use it in GitHub Desktop.
Save jeremycohensolal/608f2ffd0d701145e042fa678ba2c6a9 to your computer and use it in GitHub Desktop.
My favorite MySQL queries for table space analysis
/*
USE IT WITH CAUTION (could overload and impact your production server...)
Feel free to add your own suggestion
Made for a MySQL 5.5 database
*/
-- Biggest db (full schema)
SELECT
TABLE_SCHEMA,
SUM(ROUND(DATA_LENGTH/1024/1024,2)) as data_length_mb,
SUM(ROUND(INDEX_LENGTH/1024/1024,2)) as index_length_mb,
SUM(ROUND(DATA_FREE/ 1024/1024,2)) as data_free_mb,
SUM(ROUND(DATA_LENGTH/1024/1024,2) + ROUND(INDEX_LENGTH/1024/1024,2)) AS table_length_mb,
SUM(data_free) / (SUM(index_length) + SUM(data_length)) as frag_ratio
FROM information_schema.tables
WHERE
ENGINE = 'innodb'
GROUP BY TABLE_SCHEMA WITH ROLLUP;
-- Biggest tables
SELECT
TABLE_SCHEMA,
TABLE_NAME,
ROUND(DATA_LENGTH/1024/1024,2) as data_length_mb,
ROUND(INDEX_LENGTH/1024/1024,2) as index_length_mb,
ROUND(DATA_FREE/ 1024/1024,2) as data_free_mb,
ROUND(DATA_LENGTH/1024/1024,2) + ROUND(INDEX_LENGTH/1024/1024,2) AS table_length_mb,
data_free / (index_length + data_length) as frag_ratio,
ROW_FORMAT
FROM information_schema.tables
WHERE ENGINE = 'innodb'
ORDER BY table_length_mb DESC
LIMIT 20;
-- Biggest indexes
SELECT
TABLE_SCHEMA,
TABLE_NAME,
ROUND(DATA_LENGTH/1024/1024,2) as data_length_mb,
ROUND(INDEX_LENGTH/1024/1024,2) as index_length_mb,
ROUND(DATA_FREE/ 1024/1024,2) as data_free_mb,
ROUND(DATA_LENGTH/1024/1024,2) + ROUND(INDEX_LENGTH/1024/1024,2) AS table_length_mb,
data_free / (index_length + data_length) as frag_ratio,
ROW_FORMAT
FROM information_schema.tables
WHERE ENGINE = 'innodb'
ORDER BY index_length_mb DESC
LIMIT 20;
-- Indexes bigger than data set
SELECT
TABLE_SCHEMA,
TABLE_NAME,
ROUND(DATA_LENGTH/1024/1024,2) as data_length_mb,
ROUND(INDEX_LENGTH/1024/1024,2) as index_length_mb,
ROUND(DATA_FREE/ 1024/1024,2) as data_free_mb,
ROUND(DATA_LENGTH/1024/1024,2) + ROUND(INDEX_LENGTH/1024/1024,2) AS table_length_mb,
data_free / (index_length + data_length) as frag_ratio,
ROW_FORMAT
FROM information_schema.tables
WHERE
ENGINE = 'innodb'
AND INDEX_LENGTH > DATA_LENGTH
ORDER BY index_length_mb DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment