1drop table if exists t1; 2CREATE TABLE t1 ( 3id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 4a VARCHAR(200), 5b TEXT 6) ENGINE = InnoDB STATS_PERSISTENT=0; 7INSERT INTO t1 (a,b) VALUES 8('MySQL Tutorial','DBMS stands for DataBase ...') , 9('How To Use MySQL Well','After you went through a ...'), 10('Optimizing MySQL','In this tutorial we will show ...'); 11ALTER TABLE t1 ADD FULLTEXT INDEX idx_1 (a); 12Warnings: 13Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID 14ALTER TABLE t1 ADD FULLTEXT INDEX idx_2 (b); 15SHOW CREATE TABLE t1; 16Table Create Table 17t1 CREATE TABLE `t1` ( 18 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 19 `a` varchar(200) DEFAULT NULL, 20 `b` text, 21 PRIMARY KEY (`id`), 22 FULLTEXT KEY `idx_1` (`a`), 23 FULLTEXT KEY `idx_2` (`b`) 24) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 STATS_PERSISTENT=0 25START TRANSACTION; 26INSERT INTO t1 (a,b) VALUES 27('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 28('MySQL vs. YourSQL','In the following database comparison ...'), 29('MySQL Security','When configured properly, MySQL ...'); 30ROLLBACK; 31SELECT * FROM t1 WHERE MATCH (a) 32AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); 33id a b 341 MySQL Tutorial DBMS stands for DataBase ... 35select * from t1 where MATCH(a) AGAINST("+mysql +Tutorial" IN BOOLEAN MODE); 36id a b 371 MySQL Tutorial DBMS stands for DataBase ... 38select * from t1 where MATCH(b) AGAINST("+Tutorial" IN BOOLEAN MODE); 39id a b 403 Optimizing MySQL In this tutorial we will show ... 41select * from t1 where MATCH(b) AGAINST("+stands +(DataBase)" IN BOOLEAN MODE); 42id a b 431 MySQL Tutorial DBMS stands for DataBase ... 44select * from t1 where MATCH(b) AGAINST("+DataBase -(comparison)" IN BOOLEAN MODE); 45id a b 461 MySQL Tutorial DBMS stands for DataBase ... 47select *, MATCH(a) AGAINST("Optimizing MySQL" IN BOOLEAN MODE) as x from t1; 48id a b x 491 MySQL Tutorial DBMS stands for DataBase ... 0.0906190574169159 502 How To Use MySQL Well After you went through a ... 0.0906190574169159 513 Optimizing MySQL In this tutorial we will show ... 0.6961383819580078 52select *, MATCH(b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1; 53id a b x 541 MySQL Tutorial DBMS stands for DataBase ... 0 552 How To Use MySQL Well After you went through a ... 0 563 Optimizing MySQL In this tutorial we will show ... 0 57select * from t1 where MATCH a AGAINST ("+Optimiz* +Optimiz*" IN BOOLEAN MODE); 58id a b 593 Optimizing MySQL In this tutorial we will show ... 60select * from t1 where MATCH b AGAINST ('"DBMS stands"' IN BOOLEAN MODE); 61id a b 621 MySQL Tutorial DBMS stands for DataBase ... 63select * from t1 where MATCH b AGAINST ('"DBMS STANDS"' IN BOOLEAN MODE); 64id a b 651 MySQL Tutorial DBMS stands for DataBase ... 66select * from t1 where MATCH(b) AGAINST ("DataBase" WITH QUERY EXPANSION); 67id a b 681 MySQL Tutorial DBMS stands for DataBase ... 69select * from t1 where MATCH(a) AGAINST ("Security" WITH QUERY EXPANSION); 70id a b 71ALTER TABLE t1 DROP INDEX idx_1; 72ALTER TABLE t1 DROP INDEX idx_2; 73ALTER TABLE t1 ADD FULLTEXT INDEX idx_1 (a); 74ALTER TABLE t1 ADD FULLTEXT INDEX idx_2 (b); 75SELECT * FROM t1 WHERE MATCH (a) 76AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); 77id a b 781 MySQL Tutorial DBMS stands for DataBase ... 79select * from t1 where MATCH(a) AGAINST("+mysql +Tutorial" IN BOOLEAN MODE); 80id a b 811 MySQL Tutorial DBMS stands for DataBase ... 82select * from t1 where MATCH(b) AGAINST("+Tutorial" IN BOOLEAN MODE); 83id a b 843 Optimizing MySQL In this tutorial we will show ... 85select * from t1 where MATCH(b) AGAINST("+stands +(DataBase)" IN BOOLEAN MODE); 86id a b 871 MySQL Tutorial DBMS stands for DataBase ... 88select * from t1 where MATCH(b) AGAINST("+DataBase -(comparison)" IN BOOLEAN MODE); 89id a b 901 MySQL Tutorial DBMS stands for DataBase ... 91select *, MATCH(a) AGAINST("Optimizing MySQL" IN BOOLEAN MODE) as x from t1; 92id a b x 931 MySQL Tutorial DBMS stands for DataBase ... 0.0906190574169159 942 How To Use MySQL Well After you went through a ... 0.0906190574169159 953 Optimizing MySQL In this tutorial we will show ... 0.6961383819580078 96select *, MATCH(b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1; 97id a b x 981 MySQL Tutorial DBMS stands for DataBase ... 0 992 How To Use MySQL Well After you went through a ... 0 1003 Optimizing MySQL In this tutorial we will show ... 0 101select * from t1 where MATCH a AGAINST ("+Optimiz* +Optimiz*" IN BOOLEAN MODE); 102id a b 1033 Optimizing MySQL In this tutorial we will show ... 104select * from t1 where MATCH b AGAINST ('"DBMS stands"' IN BOOLEAN MODE); 105id a b 1061 MySQL Tutorial DBMS stands for DataBase ... 107select * from t1 where MATCH b AGAINST ('"DBMS STANDS"' IN BOOLEAN MODE); 108id a b 1091 MySQL Tutorial DBMS stands for DataBase ... 110select * from t1 where MATCH(b) AGAINST ("DataBase" WITH QUERY EXPANSION); 111id a b 1121 MySQL Tutorial DBMS stands for DataBase ... 113select * from t1 where MATCH(a) AGAINST ("Security" WITH QUERY EXPANSION); 114id a b 115INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...'); 116INSERT INTO t1 (a,b) VALUES 117('test proximity search, test, proximity and phrase', 118'search, with proximity innodb'); 119INSERT INTO t1 (a,b) VALUES 120('test proximity fts search, test, proximity and phrase', 121'search, with proximity innodb'); 122INSERT INTO t1 (a,b) VALUES 123('test more of proximity for fts search, test, more proximity and phrase', 124'search, with proximity innodb'); 125SELECT * FROM t1 126WHERE MATCH (a) 127AGAINST ('"proximity search"@3' IN BOOLEAN MODE); 128id a b 1298 test proximity search, test, proximity and phrase search, with proximity innodb 1309 test proximity fts search, test, proximity and phrase search, with proximity innodb 131SELECT * FROM t1 132WHERE MATCH (a) 133AGAINST ('"proximity search"@2' IN BOOLEAN MODE); 134id a b 1358 test proximity search, test, proximity and phrase search, with proximity innodb 136SELECT * FROM t1 137WHERE MATCH (b) 138AGAINST ('"proximity innodb"@4' IN BOOLEAN MODE); 139id a b 1408 test proximity search, test, proximity and phrase search, with proximity innodb 1419 test proximity fts search, test, proximity and phrase search, with proximity innodb 14210 test more of proximity for fts search, test, more proximity and phrase search, with proximity innodb 143SELECT * FROM t1 144WHERE MATCH (a) 145AGAINST ('"test proximity"@3' IN BOOLEAN MODE); 146id a b 1478 test proximity search, test, proximity and phrase search, with proximity innodb 1489 test proximity fts search, test, proximity and phrase search, with proximity innodb 14910 test more of proximity for fts search, test, more proximity and phrase search, with proximity innodb 150SELECT * FROM t1 151WHERE MATCH (a) 152AGAINST ('"more test proximity"@3' IN BOOLEAN MODE); 153id a b 15410 test more of proximity for fts search, test, more proximity and phrase search, with proximity innodb 155SELECT * FROM t1 156WHERE MATCH (a) 157AGAINST ('"more test proximity"@2' IN BOOLEAN MODE); 158id a b 159SELECT * FROM t1 160WHERE MATCH (a) 161AGAINST ('"more fts proximity"@02' IN BOOLEAN MODE); 162id a b 163SELECT * FROM t1 WHERE CONCAT(t1.a,t1.b) IN ( 164SELECT CONCAT(a,b) FROM t1 AS t2 WHERE 165MATCH (t2.a) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) 166) OR t1.id = 3 ; 167id a b 1681 MySQL Tutorial DBMS stands for DataBase ... 1693 Optimizing MySQL In this tutorial we will show ... 170SELECT * FROM t1 WHERE CONCAT(t1.a,t1.b) IN ( 171SELECT CONCAT(a,b) FROM t1 AS t2 172WHERE MATCH (t2.a) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) 173AND t2.id != 3) ; 174id a b 1751 MySQL Tutorial DBMS stands for DataBase ... 176SELECT * FROM t1 WHERE id IN (SELECT MIN(id) FROM t1 WHERE 177MATCH (b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) OR id = 3 ; 178id a b 1793 Optimizing MySQL In this tutorial we will show ... 180SELECT * FROM t1 WHERE id NOT IN (SELECT MIN(id) FROM t1 181WHERE MATCH (b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) ; 182id a b 1831 MySQL Tutorial DBMS stands for DataBase ... 1842 How To Use MySQL Well After you went through a ... 1857 test query expansion for database ... 1868 test proximity search, test, proximity and phrase search, with proximity innodb 1879 test proximity fts search, test, proximity and phrase search, with proximity innodb 18810 test more of proximity for fts search, test, more proximity and phrase search, with proximity innodb 189SELECT * FROM t1 WHERE EXISTS (SELECT t2.id FROM t1 AS t2 WHERE 190MATCH (t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) 191AND t1.id = t2.id) ; 192id a b 1933 Optimizing MySQL In this tutorial we will show ... 194SELECT * FROM t1 WHERE NOT EXISTS (SELECT t2.id FROM t1 AS t2 WHERE 195MATCH (t2.a) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) 196AND t1.id = t2.id) ; 197id a b 1982 How To Use MySQL Well After you went through a ... 1993 Optimizing MySQL In this tutorial we will show ... 2007 test query expansion for database ... 2018 test proximity search, test, proximity and phrase search, with proximity innodb 2029 test proximity fts search, test, proximity and phrase search, with proximity innodb 20310 test more of proximity for fts search, test, more proximity and phrase search, with proximity innodb 204SELECT * FROM t1 WHERE t1.id = (SELECT MAX(t2.id) FROM t1 AS t2 WHERE 205MATCH(t2.a) AGAINST ('"proximity search"@3' IN BOOLEAN MODE)); 206id a b 2079 test proximity fts search, test, proximity and phrase search, with proximity innodb 208SELECT * FROM t1 WHERE t1.id > (SELECT MIN(t2.id) FROM t1 AS t2 WHERE 209MATCH(t2.b) AGAINST ('"proximity innodb"@3' IN BOOLEAN MODE)); 210id a b 2119 test proximity fts search, test, proximity and phrase search, with proximity innodb 21210 test more of proximity for fts search, test, more proximity and phrase search, with proximity innodb 213DROP TABLE t1; 214