Unused Space

This is visible as Data_free in the output of SHOW TABLE STATUS. You can show data for all tables using the query below...

SELECT table_name,

       FORMAT(data_length/POWER(1024,2),0) AS table_noidx_MB,

       FORMAT(SUM(data_length+index_length)/POWER(1024,2),0) AS table_size_MB,

       FORMAT(data_free/POWER(1024,2),0) AS table_free_MB,

       FORMAT(SUM(data_length+index_length)/POWER(1024,3),0) AS table_size_GB,

       FORMAT(data_free/POWER(1024,3),0) AS table_free_GB

  FROM information_schema.tables

 WHERE table_schema='alfresco_uat'

GROUP BY table_name

 ORDER BY data_free DESC;

To reclaim space you could Optimize the table (see later on this page).

CREATE TABLE myTable (myVarcharCol VARCHAR(20), myCharCol CHAR(1), myDateCol DATE);

A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed...

CREATE TEMPORARY TABLE myTable (myVarcharCol VARCHAR(20), myCharCol CHAR(1), myDateCol DATE);

An example including indexes and constraints...

CREATE TABLE myTable ( myInt  INT AUTO_INCREMENT, 

                       myVC1  VARCHAR(30) NOT NULL, 

                       myVC2  VARCHAR(30) NOT NULL, 

                       first  VARCHAR(30),

                       last   VARCHAR(30), 

       PRIMARY KEY(myInt), 

       UNIQUE(myVC1), 

       INDEX myVC2i(myVC2) INVISIBLE, 

       INDEX name(first, last) COMMENT 'first and/or last' ); 

INNODB_DEFAULT_ROW_FORMAT

Default: DYNAMIC

SELECT @@innodb_default_row_format;

You can override the default row format during a CREATE or ALTER TABLE operation if required. 

• DYNAMIC - The DYNAMIC row format offers the same storage characteristics as the COMPACT row format but adds enhanced storage capabilities for long variable-length columns and supports large index key prefixes (1). Valid for Barracuda File Format only.

• COMPACT - The COMPACT row format reduces row storage space by about 20% compared to the REDUNDANT row format, at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed, COMPACT format is likely to be faster. If the workload is limited by CPU speed, compact format might be slower (1). Valid for Antelope and Barracuda File Formats.

• REDUNDANT - The REDUNDANT format provides compatibility with older versions of MySQL (1). Valid for Antelope and Barracuda File Formats.

The COMPRESSED row format, which is not supported for use in the system tablespace, cannot be defined as the default. The COMPRESSED row format offers the same storage characteristics and capabilities as the DYNAMIC row format but adds support for table and index data compression (1). Valid for Barracuda File Format only.

To see the row format of a specific table...

SELECT *
  FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
  WHERE NAME LIKE 'mydb/mytable' \G

To ovverride the default row format at table level...

ALTER TABLE mytable ROW_FORMAT=COMPRESSED;

INNODB_FILE_PER_TABLE

Check

SELECT @@innodb_file_per_table;

This configuration option controls whether each table has its own .frm and .ibd files in your database directory.

• 1 - ON

• 0 - OFF

INNODB_CHECKSUM_ALGORITHM

Specifies how to generate and verify the checksum stored in the disk blocks of InnoDB tablespaces. crc32 is the default value as of MySQL 5.7.7. (2)

Optimize

Percona

If you have Percona tools installed you can use this to optimize tables without locking the table...

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydatabase, t=mytable --execute

Rebuilding or Repairing

To rebuild by dumping and reloading...

mysqldump myDatabase myTable > MyDumpFile.sql

mysql myDatabase < MyDumpFile.sql

To rebuild an InnoDB Table using ALTER TABLE...

ALTER TABLE myTable ENGINE = InnoDB;

The REPAIR TABLE command can be used for non InnoDB table types (MyISAM, ARCHIVE, CSV)