MySQL loose space analysis - Query to analyse the loose space on a database.

loose_space_analysis.sql

SELECT
  CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`') AS `OBJECT NAME`,
  ROUND((DATA_LENGTH / 1024 / 1024), 2) AS `OBJECT SIZE (MB)`,
  `RATIO`,
  ROUND((DATA_FREE / 1024 / 1024), 2) AS `LOOSE SPACE (MB)`,
  `DDL`
FROM
  (
    SELECT
      TABLE_SCHEMA,
      TABLE_NAME,
      TABLE_ROWS,
      DATA_LENGTH,
      DATA_FREE,
      DATA_FREE / DATA_LENGTH AS `RATIO`,
      CONCAT('OPTIMIZE NO_WRITE_TO_BINLOG TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;') AS `DDL`
    FROM
      information_schema.TABLES
    WHERE
      TABLE_SCHEMA != 'mysql'
      AND 
      DATA_LENGTH > 0
      AND
      DATA_FREE > 0
  ) t
ORDER BY `RATIO` DESC;