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