Viewing Compression Ratio Statistics

Hyperstage provides specific statistics on table and column compression. The compression ratio is calculated in relation to the natural size of uncompressed data in the table or column. The ratio equal to n means that the compressed data, including statistics and technical description of a column, is n times smaller than its theoretical natural size.

The following natural sizes (in bytes) are defined for various data types. Note the following:

The natural size of the data type is approximately equal to the binary import/export format.

Data Types and Natural Sizes
CHAR(n), BINARY(n)	n*(number of rows)
BIGINT, INT, MEDIUMINT, SMALLINT, TINYINT, BOOL	(8 or 4 or 3 or 2 or 1 or 1)*(number of rows)
YEAR	4*(number of rows)
DATE	10*(number of rows)
TIME	8*(number of rows)
TIMESTAMP/DATETIME	19*(number of rows)
DEC(x,y)	(x+1)*(number of rows)
FLOAT	4*(number of rows)
REAL,DOUBLE	8*(number of rows)
VARCHAR(n), VARBINARY(n)	Total number of bytes used. For example, the total length of all strings, excluding terminating characters + 2*(number of rows).

Viewing Table Level Compression Ratio Statistics

To view the compression ratio at the table level, enter the following command:

mysql> show table status [from db_name] [like 'table_name'];

The optional like clause can be used to filter the tables. Note that the table name must be provided in single quotation marks (‘).

The compression statistics are provided in the table comment. For example:

mysql> show table status from test like 't1' \G
*********************** 1. Row **********************
       Name                  : t1
       Engine                : BRIGHTHOUSE
       Version               : 10
       Row_format            : Compressed
       Rows                  : 3430387
       Avg_row_length        : 0
       Data_length           : 0
       Max_data_length       : 0
       Index_length          : 0
       Data_free             : 0
       Auto_increment        : NULL
       Create_time           : 2008-09-04 15:31:39
       Check_time            : NULL
       Update_time           : 2008-09-04 15:35:30
       Collation             : ascii_bin
       Checksum              : NULL
       Create_options        :
       Comment               : Overall compression ratio 39.908
1 row in set (0.59 sec)

Viewing Column Compression Ratio Statistics

To view the compression ratio and the compressed size for a column, enter the following command:

mysql> show full columns from table_name …;

A database name and a column filter can be specified in optional clauses. For more information, see SHOW COLUMNS Syntax in the MySQL 5.x Reference Manual.

The compression statistics are provided in the column comment. In addition to the compression information, the comment line may also contain a unique indicator, meaning that the column has all unique values (except nulls).

Over time, the compression statistics may not reflect the actual compression if the significant use of UPDATE and DELETE is made, since the compression statistics are not updated when these commands are utilized.

For example:

mysql>  show full columns from t1 \G 
*********************** 1. Row ********************** 
        Field                : id1 
        Type                 : date 
        Collation            : NULL 
        Null                 : YES 
        Key                  :  
        Default              : NULL 
        Extra                :  
        Privileges           : select,insert,update,references 
        Comment              : size[MB]: 1.4; Ratop: 24.05 
*********************** 2. Row **********************  
        Field                : mfg_id 
        Type                 : smallint(6) 
        Collation            : NULL 
        Null                 : YES 
        Key                  :  
        Default              : NULL 
        Extra                :  
        Privileges           : select,insert,update,references 
        Comment              : size[MB]: 0.1; Ratio: 3674.18 
*********************** 3. Row **********************  
        Field                : account_no 
        Type                 : decimal;(13,0) 
        Collation            : NULL 
        Null                 : YES 
        Key                  :  
        Default              : NULL 
        Extra                :  
        Privileges           : select,insert,update,references 
        Comment              : size[MB]: 0.8; Ratio: 53.16

Comparison of Calculated Compression Ratio to Physical Size

The compression ratio calculated above will differ from the compression ratio calculated from physical sizes of files on disk. The compression ratio based on physical size will be slightly smaller, due to extra files that are generated containing statistics on the imported data, such as Knowledge Nodes. Knowledge Nodes are used to optimize query execution and are discussed further in About the Knowledge Grid.

Data Types and Natural Sizes
Data Type	Natural Size (in bytes)
CHAR(n), BINARY(n)	n*(number of rows)
BIGINT, INT, MEDIUMINT, SMALLINT, TINYINT, BOOL	(8 or 4 or 3 or 2 or 1 or 1)*(number of rows)
YEAR	4*(number of rows)
DATE	10*(number of rows)
TIME	8*(number of rows)
TIMESTAMP/DATETIME	19*(number of rows)
DEC(x,y)	(x+1)*(number of rows)
FLOAT	4*(number of rows)
REAL,DOUBLE	8*(number of rows)
VARCHAR(n), VARBINARY(n)	Total number of bytes used. For example, the total length of all strings, excluding terminating characters + 2*(number of rows).