Debian Codegraph @

mysql-test/suite/innodb_zip/r/wl6344_compress_level.result

raw
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;