Skip to content

Instantly share code, notes, and snippets.

@rluisr
Last active April 8, 2020 08:59
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save rluisr/6ffb3f72d7cb3e63276d93511b1baacf to your computer and use it in GitHub Desktop.
Save rluisr/6ffb3f72d7cb3e63276d93511b1baacf to your computer and use it in GitHub Desktop.
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