Skip to content

Instantly share code, notes, and snippets.

@rluisr
Last active Apr 8, 2020
Embed
What would you like to do?
my primary key is anti pattern? replace `db name`
SELECT DISTINCT
t.table_name,
column_name,
column_type,
table_rows,
(stat_value * @@innodb_page_size) / 1024 / 1024 AS index_size_mb
FROM
information_schema.tables t
LEFT JOIN information_schema.columns c
ON c.table_name = t.table_name
AND c.column_key = 'PRI'
AND c.data_type != 'int'
LEFT JOIN mysql.innodb_index_stats iis
ON iis.table_name = t.table_name
AND iis.index_name = 'PRIMARY'
AND iis.stat_name = 'size'
WHERE iis.database_name = 'db name'
AND t.table_schema = 'db name'
ORDER BY index_size_mb DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment