Debian Codegraph @

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

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