Debian Codegraph @

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

raw
1 SET GLOBAL INNODB_OPTIMIZE_FULLTEXT_ONLY=1;
2 CREATE TABLE articles (
3 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
4 title VARCHAR(200),
5 content TEXT
6 ) ENGINE= InnoDB;
7 CREATE FULLTEXT INDEX idx ON articles (title, content);
8 Warnings:
9 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
10 INSERT INTO articles (title, content) VALUES
11 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
12 ('How To Use MySQL Well','After you went through a ...'),
13 ('Optimizing MySQL','In this tutorial we will show ...'),
14 ('1001 MySQL Tricks','How to use full-text search engine'),
15 ('Go MySQL Tricks','How to use full text search engine');
16 SET GLOBAL innodb_ft_aux_table="test/articles";
17 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
18 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
19 1001 5 5 1 5 0
20 after 3 3 1 3 22
21 database 2 2 1 2 37
22 dbms 2 2 1 2 15
23 engine 5 6 2 5 46
24 engine 5 6 2 6 44
25 full 5 6 2 5 29
26 full 5 6 2 6 27
27 mysql 2 6 5 2 0
28 mysql 2 6 5 2 31
29 mysql 2 6 5 3 11
30 mysql 2 6 5 4 11
31 mysql 2 6 5 5 5
32 mysql 2 6 5 6 3
33 optimizing 4 4 1 4 0
34 search 5 6 2 5 39
35 search 5 6 2 6 37
36 show 4 4 1 4 42
37 stands 2 2 1 2 20
38 text 5 6 2 5 34
39 text 5 6 2 6 32
40 through 3 3 1 3 37
41 tricks 5 6 2 5 11
42 tricks 5 6 2 6 9
43 tutorial 2 4 2 2 6
44 tutorial 2 4 2 4 25
45 use 3 6 3 3 7
46 use 3 6 3 5 25
47 use 3 6 3 6 23
48 well 3 3 1 3 17
49 went 3 3 1 3 32
50 you 3 3 1 3 28
51 OPTIMIZE TABLE articles;
52 Table Op Msg_type Msg_text
53 test.articles optimize status OK
54 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
55 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
56 1001 5 5 1 5 0
57 after 3 3 1 3 22
58 database 2 2 1 2 37
59 dbms 2 2 1 2 15
60 engine 5 6 2 5 46
61 engine 5 6 2 6 44
62 full 5 6 2 5 29
63 full 5 6 2 6 27
64 mysql 2 6 5 2 0
65 mysql 2 6 5 2 31
66 mysql 2 6 5 3 11
67 mysql 2 6 5 4 11
68 mysql 2 6 5 5 5
69 mysql 2 6 5 6 3
70 optimizing 4 4 1 4 0
71 search 5 6 2 5 39
72 search 5 6 2 6 37
73 show 4 4 1 4 42
74 stands 2 2 1 2 20
75 text 5 6 2 5 34
76 text 5 6 2 6 32
77 through 3 3 1 3 37
78 tricks 5 6 2 5 11
79 tricks 5 6 2 6 9
80 tutorial 2 4 2 2 6
81 tutorial 2 4 2 4 25
82 use 3 6 3 3 7
83 use 3 6 3 5 25
84 use 3 6 3 6 23
85 well 3 3 1 3 17
86 went 3 3 1 3 32
87 you 3 3 1 3 28
88 SET SESSION debug="+d,fts_instrument_result_cache_limit";
89 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
90 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
91 1001 5 5 1 5 0
92 after 3 3 1 3 22
93 database 2 2 1 2 37
94 dbms 2 2 1 2 15
95 engine 5 6 2 5 46
96 engine 5 6 2 6 44
97 full 5 6 2 5 29
98 full 5 6 2 6 27
99 mysql 2 6 5 2 0
100 mysql 2 6 5 2 31
101 mysql 2 6 5 3 11
102 mysql 2 6 5 4 11
103 mysql 2 6 5 5 5
104 mysql 2 6 5 6 3
105 optimizing 4 4 1 4 0
106 search 5 6 2 5 39
107 search 5 6 2 6 37
108 show 4 4 1 4 42
109 stands 2 2 1 2 20
110 text 5 6 2 5 34
111 text 5 6 2 6 32
112 through 3 3 1 3 37
113 tricks 5 6 2 5 11
114 tricks 5 6 2 6 9
115 tutorial 2 4 2 2 6
116 tutorial 2 4 2 4 25
117 use 3 6 3 3 7
118 use 3 6 3 5 25
119 use 3 6 3 6 23
120 well 3 3 1 3 17
121 went 3 3 1 3 32
122 you 3 3 1 3 28
123 SET SESSION debug="-d,fts_instrument_result_cache_limit";
124 DROP TABLE articles;
125 SET GLOBAL innodb_ft_result_cache_limit=default;
126 CREATE TABLE articles (
127 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
128 title VARCHAR(200),
129 content TEXT
130 ) ENGINE= InnoDB;
131 CREATE FULLTEXT INDEX idx_t ON articles (title);
132 Warnings:
133 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
134 CREATE FULLTEXT INDEX idx_c ON articles (content);
135 INSERT INTO articles (title, content) VALUES
136 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
137 ('How To Use MySQL Well','After you went through a ...'),
138 ('Optimizing MySQL','In this tutorial we will show ...'),
139 ('1001 MySQL Tricks','How to use full-text search engine'),
140 ('Go MySQL Tricks','How to use full text search engine');
141 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
142 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
143 1001 5 5 1 5 0
144 mysql 2 6 5 2 0
145 mysql 2 6 5 3 11
146 mysql 2 6 5 4 11
147 mysql 2 6 5 5 5
148 mysql 2 6 5 6 3
149 optimizing 4 4 1 4 0
150 tricks 5 6 2 5 11
151 tricks 5 6 2 6 9
152 tutorial 2 2 1 2 6
153 use 3 3 1 3 7
154 well 3 3 1 3 17
155 after 3 3 1 3 0
156 database 2 2 1 2 22
157 dbms 2 2 1 2 0
158 engine 5 6 2 5 28
159 engine 5 6 2 6 28
160 full 5 6 2 5 11
161 full 5 6 2 6 11
162 mysql 2 2 1 2 16
163 search 5 6 2 5 21
164 search 5 6 2 6 21
165 show 4 4 1 4 25
166 stands 2 2 1 2 5
167 text 5 6 2 5 16
168 text 5 6 2 6 16
169 through 3 3 1 3 15
170 tutorial 4 4 1 4 8
171 use 5 6 2 5 7
172 use 5 6 2 6 7
173 went 3 3 1 3 10
174 you 3 3 1 3 6
175 SET GLOBAL innodb_ft_aux_table="test/articles";
176 OPTIMIZE TABLE articles;
177 Table Op Msg_type Msg_text
178 test.articles optimize status OK
179 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
180 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
181 1001 5 5 1 5 0
182 mysql 2 6 5 2 0
183 mysql 2 6 5 3 11
184 mysql 2 6 5 4 11
185 mysql 2 6 5 5 5
186 mysql 2 6 5 6 3
187 optimizing 4 4 1 4 0
188 tricks 5 6 2 5 11
189 tricks 5 6 2 6 9
190 tutorial 2 2 1 2 6
191 use 3 3 1 3 7
192 well 3 3 1 3 17
193 after 3 3 1 3 0
194 database 2 2 1 2 22
195 dbms 2 2 1 2 0
196 engine 5 6 2 5 28
197 engine 5 6 2 6 28
198 full 5 6 2 5 11
199 full 5 6 2 6 11
200 mysql 2 2 1 2 16
201 search 5 6 2 5 21
202 search 5 6 2 6 21
203 show 4 4 1 4 25
204 stands 2 2 1 2 5
205 text 5 6 2 5 16
206 text 5 6 2 6 16
207 through 3 3 1 3 15
208 tutorial 4 4 1 4 8
209 use 5 6 2 5 7
210 use 5 6 2 6 7
211 went 3 3 1 3 10
212 you 3 3 1 3 6
213 DROP TABLE articles;
214 SET NAMES utf8mb4;
215 CREATE TABLE articles (
216 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
217 title VARCHAR(200)
218 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
219 Warnings:
220 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
221 Warning 3778 'utf8mb3_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
222 CREATE FULLTEXT INDEX idx ON articles (title);
223 Warnings:
224 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
225 INSERT INTO articles (title) VALUES
226 ('相亲相爱'),('怜香惜爱'),('充满可爱'),('爱恨交织');
227 SET GLOBAL innodb_ft_aux_table="test/articles";
228 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
229 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
230 充满可爱 4 4 1 4 0
231 怜香惜爱 3 3 1 3 0
232 爱恨交织 5 5 1 5 0
233 相亲相爱 2 2 1 2 0
234 OPTIMIZE TABLE articles;
235 Table Op Msg_type Msg_text
236 test.articles optimize status OK
237 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
238 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
239 充满可爱 4 4 1 4 0
240 怜香惜爱 3 3 1 3 0
241 爱恨交织 5 5 1 5 0
242 相亲相爱 2 2 1 2 0
243 DROP TABLE articles;
244 CREATE TABLE articles (
245 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
246 title VARCHAR(200)
247 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
248 CREATE FULLTEXT INDEX idx ON articles (title);
249 Warnings:
250 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
251 INSERT INTO articles (title) VALUES
252 ('相亲相爱'),('怜香惜爱'),('充满可爱'),('爱恨交织');
253 SET GLOBAL innodb_ft_aux_table="test/articles";
254 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
255 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
256 爱恨交织 5 5 1 5 0
257 充满可爱 4 4 1 4 0
258 怜香惜爱 3 3 1 3 0
259 相亲相爱 2 2 1 2 0
260 OPTIMIZE TABLE articles;
261 Table Op Msg_type Msg_text
262 test.articles optimize status OK
263 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
264 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
265 怜香惜爱 3 3 1 3 0
266 充满可爱 4 4 1 4 0
267 相亲相爱 2 2 1 2 0
268 爱恨交织 5 5 1 5 0
269 DROP TABLE articles;
270 SET GLOBAL innodb_ft_aux_table=default;
271 SET GLOBAL INNODB_OPTIMIZE_FULLTEXT_ONLY=default;