MySQL Fragmentation Calculator

-- SQL snippet to calculate table fragmentation.
SELECT
  table_schema,
  TABLE_NAME,
  ROUND(DATA_LENGTH / 1024 / 1024) AS data_length,
  ROUND(INDEX_LENGTH / 1024 / 1024) AS index_length,
  ROUND(DATA_FREE / 1024 / 1024) AS data_free,
  CONCAT(
    ROUND(
      (
        data_free / (index_length + data_length)
      ) * 100
    ),
    '%'
  ) AS frag_ratio
FROM
  information_schema.tables
WHERE DATA_FREE > 0
AND TABLE_SCHEMA = '%%SCHEMA%%'
ORDER BY data_free / (index_length + data_length) DESC;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: