1SET GLOBAL Innodb_file_per_table=on; 2SET @save_innodb_compression_level = @@global.innodb_compression_level; 3SET global innodb_compression_level=0; 4#create table with 1K block size 5CREATE TABLE tab5 (col_1 CHAR (255) , 6col_2 VARCHAR (255), col_3 longtext, 7col_4 longtext,col_5 longtext, 8col_6 longtext , col_7 longtext , 9col_8 longtext ,col_9 longtext , 10col_10 longtext ,col_11 int auto_increment primary key) 11ENGINE = innodb ROW_FORMAT=compressed key_block_size=1; 12#create indexes 13CREATE INDEX idx1 ON tab5(col_4(10)); 14CREATE INDEX idx2 ON tab5(col_5(10)); 15CREATE INDEX idx3 ON tab5(col_6(10)); 16CREATE INDEX idx4 ON tab5(col_7(10)); 17CREATE INDEX idx5 ON tab5(col_8(10)); 18CREATE INDEX idx6 ON tab5(col_11); 19#load the with repeat function 20SET @col_1 = repeat('a', 100); 21SET @col_2 = repeat('b', 100); 22SET @col_3 = repeat('c', 100); 23SET @col_4 = repeat('d', 100); 24SET @col_5 = repeat('e', 100); 25SET @col_6 = repeat('f', 100); 26SET @col_7 = repeat('g', 100); 27SET @col_8 = repeat('h', 100); 28SET @col_9 = repeat('i', 100); 29SET @col_10 = repeat('j', 100); 30#insert 10 records 31BEGIN; 32INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 33VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 34INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 35VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 36INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 37VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 38INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 39VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 40INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 41VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 42INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 43VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 44INSERT INTO tab5(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 45VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 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); 52COMMIT; 53#set the compression level=9 (High compress) 54SET global innodb_compression_level=9; 55#create table with 1K block size 56CREATE TABLE tab6 (col_1 CHAR (255) , 57col_2 VARCHAR (255), col_3 longtext, 58col_4 longtext,col_5 longtext, 59col_6 longtext , col_7 longtext , 60col_8 longtext ,col_9 longtext , 61col_10 longtext ,col_11 int auto_increment primary key) 62ENGINE = innodb ROW_FORMAT=compressed key_block_size=1; 63#create indexes 64CREATE INDEX idx1 ON tab6(col_4(10)); 65CREATE INDEX idx2 ON tab6(col_5(10)); 66CREATE INDEX idx3 ON tab6(col_6(10)); 67CREATE INDEX idx4 ON tab6(col_7(10)); 68CREATE INDEX idx5 ON tab6(col_8(10)); 69CREATE INDEX idx6 ON tab6(col_11); 70#insert 10 records 71BEGIN; 72INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 73VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 74INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 75VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 76INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 77VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 78INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 79VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 80INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 81VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 82INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 83VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 84INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 85VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 86INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 87VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 88INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 89VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 90INSERT INTO tab6(col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10) 91VALUES (@col_1,@col_2,@col_3,@col_4,@cl_5,@col_6,@col_7,@col_8,@col_9,@col_10); 92COMMIT; 93#diff the sizes of the No compressed table and high compressed table 94SET @size=(SELECT 95(SELECT (SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) 96FROM INFORMATION_SCHEMA.TABLES 97WHERE table_name='tab5' AND ENGINE='InnoDB' AND table_schema='test') 98- 99(SELECT SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024 100FROM INFORMATION_SCHEMA.TABLES 101WHERE table_name='tab6' AND ENGINE='InnoDB' AND table_schema='test') 102FROM DUAL); 103#check the size of the table, it should not be Negative value 104#The results of this query Test pass = 1 and fail=0 105SELECT @size >= 0; 106@size >= 0 1071 108DROP TABLE tab5, tab6; 109SET GLOBAL innodb_compression_level = @save_innodb_compression_level; 110