1USE test; 2DROP TABLE IF EXISTS tab5; 3Warnings: 4Note 1051 Unknown table 'test.tab5' 5DROP TABLE IF EXISTS tab6; 6Warnings: 7Note 1051 Unknown table 'test.tab6' 8#set the other madatory flags before test starts 9SET GLOBAL Innodb_file_per_table=on; 10#set the compression level=0 (No compress) 11SET global innodb_compression_level=0; 12#check the compression level and the compressed_pages is default 13SELECT @@innodb_compression_level; 14@@innodb_compression_level 150 16SELECT @@Innodb_file_per_table; 17@@Innodb_file_per_table 181 19#create table with 1K block size 20CREATE TABLE tab5 (col_1 CHAR (255) , 21col_2 VARCHAR (255), col_3 longtext, 22col_4 longtext,col_5 longtext, 23col_6 longtext , col_7 longtext , 24col_8 longtext ,col_9 longtext , 25col_10 longtext ,col_11 int auto_increment primary key) 26ENGINE = innodb ROW_FORMAT=compressed key_block_size=1; 27#create indexes 28CREATE INDEX idx1 ON tab5(col_4(10)); 29CREATE INDEX idx2 ON tab5(col_5(10)); 30CREATE INDEX idx3 ON tab5(col_6(10)); 31CREATE INDEX idx4 ON tab5(col_7(10)); 32CREATE INDEX idx5 ON tab5(col_8(10)); 33CREATE INDEX idx6 ON tab5(col_11); 34#load the with repeat function 35SET @col_1 = repeat('a', 100); 36SET @col_2 = repeat('b', 100); 37SET @col_3 = repeat('c', 100); 38SET @col_4 = repeat('d', 100); 39SET @col_5 = repeat('e', 100); 40SET @col_6 = repeat('f', 100); 41SET @col_7 = repeat('g', 100); 42SET @col_8 = repeat('h', 100); 43SET @col_9 = repeat('i', 100); 44SET @col_10 = repeat('j', 100); 45#insert 10 records 46INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 47VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 48INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 49VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 50INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 51VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 52INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 53VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 54INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 55VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 56INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 57VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 58INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 59VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 60INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 61VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 62INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 63VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 64INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 65VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 66#set the compression level=9 (High compress) 67SET global innodb_compression_level=9; 68#create table with 1K block size 69CREATE TABLE tab6 (col_1 CHAR (255) , 70col_2 VARCHAR (255), col_3 longtext, 71col_4 longtext,col_5 longtext, 72col_6 longtext , col_7 longtext , 73col_8 longtext ,col_9 longtext , 74col_10 longtext ,col_11 int auto_increment primary key) 75ENGINE = innodb ROW_FORMAT=compressed key_block_size=1; 76#create indexes 77CREATE INDEX idx1 ON tab6(col_4(10)); 78CREATE INDEX idx2 ON tab6(col_5(10)); 79CREATE INDEX idx3 ON tab6(col_6(10)); 80CREATE INDEX idx4 ON tab6(col_7(10)); 81CREATE INDEX idx5 ON tab6(col_8(10)); 82CREATE INDEX idx6 ON tab6(col_11); 83#load the with repeat function 84SET @col_1 = repeat('a', 100); 85SET @col_2 = repeat('b', 100); 86SET @col_3 = repeat('c', 100); 87SET @col_4 = repeat('d', 100); 88SET @col_5 = repeat('e', 100); 89SET @col_6 = repeat('f', 100); 90SET @col_7 = repeat('g', 100); 91SET @col_8 = repeat('h', 100); 92SET @col_9 = repeat('i', 100); 93SET @col_10 = repeat('j', 100); 94#insert 10 records 95INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 96VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 97INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 98VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 99INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 100VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 101INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 102VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 103INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 104VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 105INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 106VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 107INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 108VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 109INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 110VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 111INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 112VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 113INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 114VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 115#diff the sizes of the No compressed table and high compressed table 116SET @size=(SELECT 117(SELECT (SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) 118FROM INFORMATION_SCHEMA.TABLES 119WHERE table_name='tab5' AND ENGINE='InnoDB' AND table_schema='test') 120- 121(SELECT SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024 122FROM INFORMATION_SCHEMA.TABLES 123WHERE table_name='tab6' AND ENGINE='InnoDB' AND table_schema='test') 124FROM DUAL); 125#check the size of the table, it should not be Negative value 126#The results of this query Test pass = 1 and fail=0 127SELECT @size >= 0; 128@size >= 0 1291 130# 131# Cleanup 132# 133DROP TABLE tab5; 134DROP TABLE tab6; 135#reset back the compression_level to default. 136