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