Debian Codegraph @

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

raw
1 SET @innodb_ft_server_stopword_table_orig=@@innodb_ft_server_stopword_table;
2 SET @innodb_ft_enable_stopword_orig=@@innodb_ft_enable_stopword;
3 SET @innodb_ft_user_stopword_table_orig=@@innodb_ft_user_stopword_table;
4 call mtr.add_suppression("\\[ERROR\\] InnoDB: user stopword table not_defined does not exist.");
5 call mtr.add_suppression("\\[ERROR\\] InnoDB: user stopword table test/user_stopword_session does not exist.");
6 select * from information_schema.innodb_ft_default_stopword;
7 value
8 a
9 about
10 an
11 are
12 as
13 at
14 be
15 by
16 com
17 de
18 en
19 for
20 from
21 how
22 i
23 in
24 is
25 it
26 la
27 of
28 on
29 or
30 that
31 the
32 this
33 to
34 was
35 what
36 when
37 where
38 who
39 will
40 with
41 und
42 the
43 www
44 CREATE TABLE articles (
45 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
46 title VARCHAR(200),
47 body TEXT,
48 FULLTEXT (title,body)
49 ) ENGINE=InnoDB;
50 INSERT INTO articles (title,body) VALUES
51 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
52 ('How To Use MySQL Well','After you went through a ...'),
53 ('Optimizing MySQL','In this tutorial we will show ...'),
54 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
55 ('MySQL vs. YourSQL','In the following database comparison ...'),
56 ('MySQL Security','When configured properly, MySQL ...');
57 SELECT * FROM articles WHERE MATCH (title,body)
58 AGAINST ('the' IN NATURAL LANGUAGE MODE);
59 id title body
60 set global innodb_ft_server_stopword_table = "not_defined";
61 ERROR 42000: Variable 'innodb_ft_server_stopword_table' can't be set to the value of 'not_defined'
62 set global innodb_ft_server_stopword_table = NULL;
63 create table user_stopword(value varchar(30)) engine = innodb;
64 set global innodb_ft_server_stopword_table = "test/user_stopword";
65 drop index title on articles;
66 create fulltext index idx on articles(title, body);
67 SELECT * FROM articles WHERE MATCH (title,body)
68 AGAINST ('the' IN NATURAL LANGUAGE MODE);
69 id title body
70 5 MySQL vs. YourSQL In the following database comparison ...
71 CREATE TABLE articles_2 (
72 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
73 title VARCHAR(200),
74 body TEXT,
75 FULLTEXT (title,body)
76 ) ENGINE=InnoDB;
77 INSERT INTO articles_2 (title, body)
78 VALUES ('test for stopwords','this is it...');
79 SELECT * FROM articles_2 WHERE MATCH (title,body)
80 AGAINST ('this' IN NATURAL LANGUAGE MODE);
81 id title body
82 1 test for stopwords this is it...
83 insert into user_stopword values("the");
84 delete from user_stopword;
85 insert into user_stopword values("this");
86 CREATE TABLE articles_3 (
87 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
88 title VARCHAR(200),
89 body TEXT,
90 FULLTEXT (title,body)
91 ) ENGINE=InnoDB;
92 INSERT INTO articles_3 (title, body)
93 VALUES ('test for stopwords','this is it...');
94 SELECT * FROM articles_3 WHERE MATCH (title,body)
95 AGAINST ('this' IN NATURAL LANGUAGE MODE);
96 id title body
97 create table user_stopword_session(value varchar(30)) engine = innodb;
98 insert into user_stopword values("this");
99 delete from user_stopword;
100 insert into user_stopword_session values("session");
101 set session innodb_ft_user_stopword_table="test/user_stopword_session";
102 CREATE TABLE articles_4 (
103 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
104 title VARCHAR(200),
105 body TEXT,
106 FULLTEXT (title,body)
107 ) ENGINE=InnoDB;
108 INSERT INTO articles_4 (title, body)
109 VALUES ('test for session stopwords','this should also be excluded...');
110 SELECT * FROM articles_4 WHERE MATCH (title,body)
111 AGAINST ('session' IN NATURAL LANGUAGE MODE);
112 id title body
113 SELECT * FROM articles_4 WHERE MATCH (title,body)
114 AGAINST ('this' IN NATURAL LANGUAGE MODE);
115 id title body
116 1 test for session stopwords this should also be excluded...
117 connect con1,localhost,root,,;
118 CREATE TABLE articles_5 (
119 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
120 title VARCHAR(200),
121 body TEXT,
122 FULLTEXT (title,body)
123 ) ENGINE=InnoDB;
124 INSERT INTO articles_5 (title, body)
125 VALUES ('test for session stopwords','this should also be excluded...');
126 SELECT * FROM articles_5 WHERE MATCH (title,body)
127 AGAINST ('session' IN NATURAL LANGUAGE MODE);
128 id title body
129 1 test for session stopwords this should also be excluded...
130 connection default;
131 drop table articles;
132 drop table articles_2;
133 drop table articles_3;
134 drop table articles_4;
135 drop table articles_5;
136 drop table user_stopword;
137 drop table user_stopword_session;
138 SET GLOBAL innodb_ft_enable_stopword=@innodb_ft_enable_stopword_orig;
139 SET GLOBAL innodb_ft_server_stopword_table=default;
140 CREATE TABLE articles (
141 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
142 title VARCHAR(200),
143 body TEXT,
144 FULLTEXT `idx` (title,body)
145 ) ENGINE=InnoDB;
146 SHOW CREATE TABLE articles;
147 Table Create Table
148 articles CREATE TABLE `articles` (
149 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
150 `title` varchar(200) DEFAULT NULL,
151 `body` text DEFAULT NULL,
152 PRIMARY KEY (`id`),
153 FULLTEXT KEY `idx` (`title`,`body`)
154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
155 INSERT INTO articles (title,body) VALUES
156 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
157 ('when To Use MySQL Well','After that you went through a ...'),
158 ('where will Optimizing MySQL','In what tutorial we will show ...'),
159 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
160 ('MySQL vs. YourSQL','In the following database comparison ...'),
161 ('MySQL Security','When configured properly, MySQL ...');
162 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
163 id title body
164 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
165 id title body
166 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
167 id title body
168 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
169 id title body
170 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
171 id title body
172 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
173 id title body
174 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
175 id title body
176 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
177 id title body
178 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
179 id title body
180 INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words');
181 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
182 id title body
183 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
184 id title body
185 UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
186 WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
187 UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
188 WHERE id = 7;
189 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
190 id title body
191 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
192 id title body
193 DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
194 SELECT * FROM articles WHERE id = 7;
195 id title body
196 7 update the record to see will is indexed or not
197 DELETE FROM articles WHERE id = 7;
198 SET global innodb_ft_server_stopword_table = NULL;
199 SET SESSION innodb_ft_enable_stopword = 0;
200 select @@innodb_ft_enable_stopword;
201 @@innodb_ft_enable_stopword
202 0
203 SET global innodb_ft_user_stopword_table = NULL;
204 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
205 id title body
206 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
207 id title body
208 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
209 id title body
210 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
211 id title body
212 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
213 id title body
214 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
215 id title body
216 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
217 id title body
218 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
219 id title body
220 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
221 id title body
222 INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words');
223 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
224 id title body
225 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
226 id title body
227 UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
228 WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
229 UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
230 WHERE id = 8;
231 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
232 id title body
233 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
234 id title body
235 SELECT * FROM articles WHERE id = 8;
236 id title body
237 8 update the record to see will is indexed or not
238 DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
239 SELECT * FROM articles WHERE id = 8;
240 id title body
241 8 update the record to see will is indexed or not
242 DELETE FROM articles WHERE id = 8;
243 ALTER TABLE articles DROP INDEX idx;
244 SHOW CREATE TABLE articles;
245 Table Create Table
246 articles CREATE TABLE `articles` (
247 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
248 `title` varchar(200) DEFAULT NULL,
249 `body` text DEFAULT NULL,
250 PRIMARY KEY (`id`)
251 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
252 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
253 ANALYZE TABLE articles;
254 Table Op Msg_type Msg_text
255 test.articles analyze status Engine-independent statistics collected
256 test.articles analyze Warning Engine-independent statistics are not collected for column 'body'
257 test.articles analyze status OK
258 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
259 id title body
260 3 where will Optimizing MySQL In what tutorial we will show ...
261 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
262 id title body
263 2 when To Use MySQL Well After that you went through a ...
264 6 MySQL Security When configured properly, MySQL ...
265 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
266 id title body
267 3 where will Optimizing MySQL In what tutorial we will show ...
268 1 MySQL from Tutorial DBMS stands for DataBase ...
269 6 MySQL Security When configured properly, MySQL ...
270 2 when To Use MySQL Well After that you went through a ...
271 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
272 5 MySQL vs. YourSQL In the following database comparison ...
273 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
274 id title body
275 2 when To Use MySQL Well After that you went through a ...
276 3 where will Optimizing MySQL In what tutorial we will show ...
277 6 MySQL Security When configured properly, MySQL ...
278 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
279 id title body
280 3 where will Optimizing MySQL In what tutorial we will show ...
281 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
282 id title body
283 1 MySQL from Tutorial DBMS stands for DataBase ...
284 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
285 id title body
286 3 where will Optimizing MySQL In what tutorial we will show ...
287 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
288 id title body
289 3 where will Optimizing MySQL In what tutorial we will show ...
290 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
291 id title body
292 3 where will Optimizing MySQL In what tutorial we will show ...
293 INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words');
294 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
295 id title body
296 9 the record will not index the , will words
297 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
298 id title body
299 9 the record will not index the , will words
300 UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
301 WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
302 SELECT COUNT(*),max(id) FROM articles;
303 COUNT(*) max(id)
304 7 9
305 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
306 id title body
307 9 update the record to see will is indexed or not
308 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
309 id title body
310 3 where will Optimizing MySQL In what tutorial we will show ...
311 9 update the record to see will is indexed or not
312 DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
313 SELECT * FROM articles WHERE id = 9;
314 id title body
315 DROP TABLE articles;
316 SET SESSION innodb_ft_enable_stopword=@innodb_ft_enable_stopword_orig;
317 SET GLOBAL innodb_ft_server_stopword_table=@innodb_ft_server_stopword_table_orig;
318 SET GLOBAL innodb_ft_user_stopword_table=@innodb_ft_user_stopword_table_orig;
319 SET SESSION innodb_ft_user_stopword_table=default;
320 CREATE TABLE articles (
321 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
322 title VARCHAR(200),
323 body TEXT,
324 FULLTEXT `idx` (title,body)
325 ) ENGINE=InnoDB;
326 INSERT INTO articles (title,body) VALUES
327 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
328 ('when To Use MySQL Well','After that you went through a ...'),
329 ('where will Optimizing MySQL','In what tutorial we will show ...'),
330 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
331 ('MySQL vs. YourSQL','In the following database comparison ...'),
332 ('MySQL Security','When configured properly, MySQL ...');
333 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
334 id title body
335 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
336 id title body
337 create table user_stopword(value varchar(30)) engine = innodb;
338 set session innodb_ft_user_stopword_table = "test/user_stopword";
339 create table server_stopword(value varchar(30)) engine = innodb;
340 set global innodb_ft_server_stopword_table = "test/server_stopword";
341 insert into user_stopword values("when"),("where");
342 delete from user_stopword;
343 insert into user_stopword values("this"),("will"),("the");
344 ALTER TABLE articles DROP INDEX idx;
345 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
346 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
347 id title body
348 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
349 id title body
350 insert into server_stopword values("what"),("where");
351 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
352 id title body
353 3 where will Optimizing MySQL In what tutorial we will show ...
354 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
355 id title body
356 3 where will Optimizing MySQL In what tutorial we will show ...
357 DELETE FROM user_stopword;
358 ALTER TABLE articles DROP INDEX idx;
359 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
360 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
361 id title body
362 3 where will Optimizing MySQL In what tutorial we will show ...
363 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
364 id title body
365 3 where will Optimizing MySQL In what tutorial we will show ...
366 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
367 id title body
368 3 where will Optimizing MySQL In what tutorial we will show ...
369 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
370 id title body
371 3 where will Optimizing MySQL In what tutorial we will show ...
372 insert into user_stopword values("this"),("will"),("the");
373 ALTER TABLE articles DROP INDEX idx;
374 SET SESSION innodb_ft_enable_stopword = 0;
375 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
376 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
377 id title body
378 3 where will Optimizing MySQL In what tutorial we will show ...
379 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
380 id title body
381 3 where will Optimizing MySQL In what tutorial we will show ...
382 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
383 id title body
384 3 where will Optimizing MySQL In what tutorial we will show ...
385 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
386 id title body
387 3 where will Optimizing MySQL In what tutorial we will show ...
388 SET SESSION innodb_ft_enable_stopword = 1;
389 ALTER TABLE articles DROP INDEX idx;
390 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
391 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
392 id title body
393 3 where will Optimizing MySQL In what tutorial we will show ...
394 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
395 id title body
396 3 where will Optimizing MySQL In what tutorial we will show ...
397 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
398 id title body
399 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
400 id title body
401 SET SESSION innodb_ft_enable_stopword = 1;
402 SET SESSION innodb_ft_user_stopword_table = default;
403 ALTER TABLE articles DROP INDEX idx;
404 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
405 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
406 id title body
407 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
408 id title body
409 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
410 id title body
411 3 where will Optimizing MySQL In what tutorial we will show ...
412 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
413 id title body
414 3 where will Optimizing MySQL In what tutorial we will show ...
415 DROP TABLE articles,user_stopword,server_stopword;
416 SET innodb_ft_enable_stopword=@innodb_ft_enable_stopword_orig;
417 SET GLOBAL innodb_ft_server_stopword_table=default;
418 SET SESSION innodb_ft_user_stopword_table=default;
419 CREATE TABLE articles (
420 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
421 title VARCHAR(200),
422 body TEXT,
423 FULLTEXT `idx` (title,body)
424 ) ENGINE=InnoDB;
425 SHOW CREATE TABLE articles;
426 Table Create Table
427 articles CREATE TABLE `articles` (
428 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
429 `title` varchar(200) DEFAULT NULL,
430 `body` text DEFAULT NULL,
431 PRIMARY KEY (`id`),
432 FULLTEXT KEY `idx` (`title`,`body`)
433 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
434 INSERT INTO articles (title,body) VALUES
435 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
436 ('when To Use MySQL Well','After that you went through a ...'),
437 ('where will Optimizing MySQL','In what tutorial we will show ...'),
438 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
439 ('MySQL vs. YourSQL','In the following database comparison ...'),
440 ('MySQL Security','When configured properly, MySQL ...');
441 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
442 id title body
443 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
444 id title body
445 create table user_stopword(value varchar(30)) engine = innodb;
446 set session innodb_ft_user_stopword_table = "test/user_stopword";
447 insert into user_stopword values("mysqld"),("DBMS");
448 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
449 id title body
450 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
451 id title body
452 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE);
453 id title body
454 1 MySQL from Tutorial DBMS stands for DataBase ...
455 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld');
456 id title body
457 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
458 ALTER TABLE articles DROP INDEX idx;
459 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
460 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
461 id title body
462 3 where will Optimizing MySQL In what tutorial we will show ...
463 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
464 id title body
465 3 where will Optimizing MySQL In what tutorial we will show ...
466 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE);
467 id title body
468 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld');
469 id title body
470 set session innodb_ft_user_stopword_table = default;
471 create table server_stopword(value varchar(30)) engine = innodb;
472 set global innodb_ft_server_stopword_table = "test/server_stopword";
473 insert into server_stopword values("root"),("properly");
474 ALTER TABLE articles DROP INDEX idx;
475 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
476 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
477 id title body
478 3 where will Optimizing MySQL In what tutorial we will show ...
479 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
480 id title body
481 3 where will Optimizing MySQL In what tutorial we will show ...
482 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE);
483 id title body
484 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly');
485 id title body
486 set session innodb_ft_user_stopword_table = "test/user_stopword";
487 set global innodb_ft_server_stopword_table = "test/server_stopword";
488 ALTER TABLE articles DROP INDEX idx;
489 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
490 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
491 id title body
492 3 where will Optimizing MySQL In what tutorial we will show ...
493 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
494 id title body
495 3 where will Optimizing MySQL In what tutorial we will show ...
496 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE);
497 id title body
498 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
499 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly');
500 id title body
501 6 MySQL Security When configured properly, MySQL ...
502 set session innodb_ft_user_stopword_table = "test/user_stopword";
503 DELETE FROM user_stopword;
504 set global innodb_ft_server_stopword_table = "test/server_stopword";
505 DELETE FROM server_stopword;
506 ALTER TABLE articles DROP INDEX idx;
507 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
508 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
509 id title body
510 3 where will Optimizing MySQL In what tutorial we will show ...
511 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
512 id title body
513 3 where will Optimizing MySQL In what tutorial we will show ...
514 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE);
515 id title body
516 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
517 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly');
518 id title body
519 6 MySQL Security When configured properly, MySQL ...
520 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE);
521 id title body
522 1 MySQL from Tutorial DBMS stands for DataBase ...
523 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld');
524 id title body
525 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
526 DROP TABLE articles,user_stopword,server_stopword;
527 SET SESSION innodb_ft_enable_stopword=@innodb_ft_enable_stopword_orig;
528 SET GLOBAL innodb_ft_server_stopword_table=default;
529 SET SESSION innodb_ft_user_stopword_table=default;
530 CREATE TABLE articles (
531 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
532 title VARCHAR(200),
533 body TEXT,
534 FULLTEXT `idx` (title,body)
535 ) ENGINE=InnoDB;
536 SHOW CREATE TABLE articles;
537 Table Create Table
538 articles CREATE TABLE `articles` (
539 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
540 `title` varchar(200) DEFAULT NULL,
541 `body` text DEFAULT NULL,
542 PRIMARY KEY (`id`),
543 FULLTEXT KEY `idx` (`title`,`body`)
544 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
545 INSERT INTO articles (title,body) VALUES
546 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
547 ('when To Use MySQL Well','After that you went through a ...'),
548 ('where will Optimizing MySQL','In what tutorial we will show ...'),
549 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
550 ('MySQL vs. YourSQL','In the following database comparison ...'),
551 ('MySQL Security','When configured properly, MySQL ...');
552 SET SESSION innodb_ft_enable_stopword = 0;
553 select @@innodb_ft_enable_stopword;
554 @@innodb_ft_enable_stopword
555 0
556 ALTER TABLE articles DROP INDEX idx;
557 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
558 "In connection 1"
559 connection con1;
560 select @@innodb_ft_enable_stopword;
561 @@innodb_ft_enable_stopword
562 1
563 ANALYZE TABLE articles;
564 Table Op Msg_type Msg_text
565 test.articles analyze status Engine-independent statistics collected
566 test.articles analyze Warning Engine-independent statistics are not collected for column 'body'
567 test.articles analyze status OK
568 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
569 id title body
570 3 where will Optimizing MySQL In what tutorial we will show ...
571 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
572 id title body
573 2 when To Use MySQL Well After that you went through a ...
574 6 MySQL Security When configured properly, MySQL ...
575 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
576 id title body
577 3 where will Optimizing MySQL In what tutorial we will show ...
578 1 MySQL from Tutorial DBMS stands for DataBase ...
579 6 MySQL Security When configured properly, MySQL ...
580 2 when To Use MySQL Well After that you went through a ...
581 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
582 5 MySQL vs. YourSQL In the following database comparison ...
583 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
584 id title body
585 2 when To Use MySQL Well After that you went through a ...
586 3 where will Optimizing MySQL In what tutorial we will show ...
587 6 MySQL Security When configured properly, MySQL ...
588 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
589 id title body
590 3 where will Optimizing MySQL In what tutorial we will show ...
591 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
592 id title body
593 1 MySQL from Tutorial DBMS stands for DataBase ...
594 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
595 id title body
596 3 where will Optimizing MySQL In what tutorial we will show ...
597 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
598 id title body
599 3 where will Optimizing MySQL In what tutorial we will show ...
600 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
601 id title body
602 3 where will Optimizing MySQL In what tutorial we will show ...
603 SET SESSION innodb_ft_enable_stopword = 1;
604 select @@innodb_ft_enable_stopword;
605 @@innodb_ft_enable_stopword
606 1
607 ALTER TABLE articles DROP INDEX idx;
608 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
609 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
610 id title body
611 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
612 id title body
613 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
614 id title body
615 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
616 id title body
617 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
618 id title body
619 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
620 id title body
621 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
622 id title body
623 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
624 id title body
625 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
626 id title body
627 "In connection default"
628 connection default;
629 select @@innodb_ft_enable_stopword;
630 @@innodb_ft_enable_stopword
631 0
632 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
633 id title body
634 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
635 id title body
636 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
637 id title body
638 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
639 id title body
640 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
641 id title body
642 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
643 id title body
644 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
645 id title body
646 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
647 id title body
648 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
649 id title body
650 INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words');
651 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
652 id title body
653 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
654 id title body
655 SET SESSION innodb_ft_enable_stopword = 1;
656 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
657 id title body
658 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
659 id title body
660 "In connection 1"
661 connection con1;
662 SET SESSION innodb_ft_enable_stopword = 1;
663 create table user_stopword(value varchar(30)) engine = innodb;
664 set session innodb_ft_user_stopword_table = "test/user_stopword";
665 insert into user_stopword values("this"),("will"),("the");
666 ALTER TABLE articles DROP INDEX idx;
667 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
668 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
669 id title body
670 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
671 id title body
672 "In connection default"
673 connection default;
674 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
675 id title body
676 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
677 id title body
678 select @@innodb_ft_user_stopword_table;
679 @@innodb_ft_user_stopword_table
680 NULL
681 create table user_stopword_1(value varchar(30)) engine = innodb;
682 set session innodb_ft_user_stopword_table = "test/user_stopword_1";
683 insert into user_stopword_1 values("when");
684 SET SESSION innodb_ft_enable_stopword = 1;
685 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE);
686 id title body
687 2 when To Use MySQL Well After that you went through a ...
688 6 MySQL Security When configured properly, MySQL ...
689 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('when');
690 id title body
691 2 when To Use MySQL Well After that you went through a ...
692 6 MySQL Security When configured properly, MySQL ...
693 ALTER TABLE articles DROP INDEX idx;
694 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
695 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE);
696 id title body
697 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('when');
698 id title body
699 "In connection 1"
700 connection con1;
701 SET SESSION innodb_ft_enable_stopword = 1;
702 SET SESSION innodb_ft_user_stopword_table=default;
703 select @@innodb_ft_user_stopword_table;
704 @@innodb_ft_user_stopword_table
705 NULL
706 select @@innodb_ft_server_stopword_table;
707 @@innodb_ft_server_stopword_table
708 NULL
709 create table server_stopword(value varchar(30)) engine = innodb;
710 SET GLOBAL innodb_ft_server_stopword_table = "test/server_stopword";
711 select @@innodb_ft_server_stopword_table;
712 @@innodb_ft_server_stopword_table
713 test/server_stopword
714 insert into server_stopword values("when"),("the");
715 ALTER TABLE articles DROP INDEX idx;
716 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
717 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE);
718 id title body
719 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the');
720 id title body
721 disconnect con1;
722 "In connection default"
723 connection default;
724 SET SESSION innodb_ft_enable_stopword = 1;
725 SET SESSION innodb_ft_user_stopword_table=default;
726 select @@innodb_ft_server_stopword_table;
727 @@innodb_ft_server_stopword_table
728 test/server_stopword
729 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE);
730 id title body
731 3 where will Optimizing MySQL In what tutorial we will show ...
732 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where');
733 id title body
734 3 where will Optimizing MySQL In what tutorial we will show ...
735 insert into server_stopword values("where"),("will");
736 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE);
737 id title body
738 3 where will Optimizing MySQL In what tutorial we will show ...
739 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where');
740 id title body
741 3 where will Optimizing MySQL In what tutorial we will show ...
742 ALTER TABLE articles DROP INDEX idx;
743 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
744 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE);
745 id title body
746 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the');
747 id title body
748 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE);
749 id title body
750 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where');
751 id title body
752 DROP TABLE articles,user_stopword,user_stopword_1,server_stopword;
753 SET GLOBAL innodb_ft_user_stopword_table=@innodb_ft_user_stopword_table_orig;
754 SET GLOBAL innodb_ft_server_stopword_table=@innodb_ft_server_stopword_table_orig;