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