Debian Codegraph @

mysql-test/suite/innodb_fts/r/optimize_big.result

raw
1 SET GLOBAL innodb_optimize_fulltext_only=ON;
2 CREATE TABLE book (
3 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
4 heading VARCHAR(20),
5 content TEXT,
6 FULLTEXT idx (content)
7 ) ENGINE= InnoDB;
8 CREATE PROCEDURE find (word CHAR(20))
9 SELECT heading FROM book WHERE MATCH(content)
10 AGAINST (word IN NATURAL LANGUAGE MODE)
11 ORDER BY id;
12 LOAD DATA INFILE '<MYSQLTEST_VARDIR>/std_data/lob/frankenstein.txt' INTO TABLE book
13 FIELDS TERMINATED BY '#' LINES TERMINATED BY '@' (heading, content);
14 CALL find('they');
15 heading
16 Letter 1
17 Letter 2
18 Letter 4
19 Chapter 1
20 Chapter 2
21 Chapter 3
22 Chapter 5
23 Chapter 6
24 Chapter 7
25 Chapter 8
26 Chapter 9
27 Chapter 10
28 Chapter 11
29 Chapter 12
30 Chapter 13
31 Chapter 14
32 Chapter 15
33 Chapter 16
34 Chapter 17
35 Chapter 18
36 Chapter 19
37 Chapter 20
38 Chapter 21
39 Chapter 22
40 Chapter 23
41 Chapter 24
42 CALL find('wretched');
43 heading
44 Letter 4
45 Chapter 5
46 Chapter 7
47 Chapter 8
48 Chapter 9
49 Chapter 10
50 Chapter 11
51 Chapter 12
52 Chapter 15
53 Chapter 20
54 Chapter 21
55 Chapter 24
56 CALL find('mariner');
57 heading
58 Letter 2
59 Chapter 5
60 SET GLOBAL innodb_ft_aux_table="test/book";
61 OPTIMIZE TABLE book;
62 Table Op Msg_type Msg_text
63 test.book optimize status OK
64 CALL find('they');
65 heading
66 Letter 1
67 Letter 2
68 Letter 4
69 Chapter 1
70 Chapter 2
71 Chapter 3
72 Chapter 5
73 Chapter 6
74 Chapter 7
75 Chapter 8
76 Chapter 9
77 Chapter 10
78 Chapter 11
79 Chapter 12
80 Chapter 13
81 Chapter 14
82 Chapter 15
83 Chapter 16
84 Chapter 17
85 Chapter 18
86 Chapter 19
87 Chapter 20
88 Chapter 21
89 Chapter 22
90 Chapter 23
91 Chapter 24
92 CALL find('wretched');
93 heading
94 Letter 4
95 Chapter 5
96 Chapter 7
97 Chapter 8
98 Chapter 9
99 Chapter 10
100 Chapter 11
101 Chapter 12
102 Chapter 15
103 Chapter 20
104 Chapter 21
105 Chapter 24
106 CALL find('mariner');
107 heading
108 Letter 2
109 Chapter 5
110 SET GLOBAL innodb_ft_aux_table="test/book";
111 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
112 count(*)
113 47753
114 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
115 count(*)
116 0
117 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
118 count(*)
119 0
120 DELETE FROM book;
121 CALL find('they');
122 heading
123 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
124 count(*)
125 28
126 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
127 count(*)
128 47753
129 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
130 count(*)
131 0
132 OPTIMIZE TABLE book;
133 Table Op Msg_type Msg_text
134 test.book optimize status OK
135 CALL find('they');
136 heading
137 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
138 count(*)
139 28
140 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
141 count(*)
142 33876
143 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
144 count(*)
145 0
146 SET GLOBAL innodb_ft_num_word_optimize=10000;
147 OPTIMIZE TABLE book;
148 Table Op Msg_type Msg_text
149 test.book optimize status OK
150 OPTIMIZE TABLE book;
151 Table Op Msg_type Msg_text
152 test.book optimize status OK
153 CALL find('they');
154 heading
155 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
156 count(*)
157 0
158 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
159 count(*)
160 0
161 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
162 count(*)
163 0
164 DROP TABLE book;
165 DROP PROCEDURE find;
166 SET GLOBAL innodb_optimize_fulltext_only=OFF;
167 SET GLOBAL innodb_ft_aux_table=default;
168 SET GLOBAL innodb_ft_num_word_optimize=default;