Debian Codegraph @

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

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