drop table if exists t1,t2,t3; CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), ('Full-text indexes', 'are called collections'), ('Only MyISAM tables','support collections'), ('Function MATCH ... AGAINST()','is used to do a search'), ('Full-text search in MySQL', 'implements vector space model'); select * from t1 where MATCH(a,b) AGAINST ("collections"); a b Only MyISAM tables support collections Full-text indexes are called collections select * from t1 where MATCH(a,b) AGAINST ("indexes"); a b Full-text indexes are called collections select * from t1 where MATCH(a,b) AGAINST ("indexes collections"); a b Full-text indexes are called collections Only MyISAM tables support collections select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes"); a b Only MyISAM tables support collections Full-text indexes are called collections Full-text indexes are called collections select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); a b MySQL has now support for full-text search select * from t1 where MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE); a b MySQL has now support for full-text search Full-text indexes are called collections Only MyISAM tables support collections select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE); a b Full-text indexes are called collections Only MyISAM tables support collections select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE); a b MySQL has now support for full-text search Function MATCH ... AGAINST() is used to do a search Full-text search in MySQL implements vector space model select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); a b Only MyISAM tables support collections select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); a b MySQL has now support for full-text search Function MATCH ... AGAINST() is used to do a search Full-text search in MySQL implements vector space model select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); a b MySQL has now support for full-text search Full-text search in MySQL implements vector space model select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); a b Function MATCH ... AGAINST() is used to do a search select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1; a b x MySQL has now support for full-text search 1 Full-text indexes are called collections 1 Only MyISAM tables support collections 2 Function MATCH ... AGAINST() is used to do a search 0 Full-text search in MySQL implements vector space model 0 select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1; a b x MySQL has now support for full-text search 1 Full-text indexes are called collections 1 Only MyISAM tables support collections 2 Function MATCH ... AGAINST() is used to do a search 0 Full-text search in MySQL implements vector space model 0 select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE); a b Full-text indexes are called collections select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); a b MySQL has now support for full-text search select * from t1 where MATCH a,b AGAINST ('"text search" "now support"' IN BOOLEAN MODE); a b MySQL has now support for full-text search Full-text search in MySQL implements vector space model select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN BOOLEAN MODE); a b Full-text search in MySQL implements vector space model select * from t1 where MATCH a,b AGAINST ('"text search" +"now support"' IN BOOLEAN MODE); a b MySQL has now support for full-text search select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE); a b Full-text search in MySQL implements vector space model select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE); a b MySQL has now support for full-text search Function MATCH ... AGAINST() is used to do a search delete from t1 where a like "MySQL%"; update t1 set a='some test foobar' where MATCH a,b AGAINST ('model'); delete from t1 where MATCH(a,b) AGAINST ("indexes"); select * from t1; a b Only MyISAM tables support collections Function MATCH ... AGAINST() is used to do a search some test foobar implements vector space model drop table t1; CREATE TABLE t1 ( id int(11), ticket int(11), KEY ti (id), KEY tit (ticket) ); INSERT INTO t1 VALUES (2,3),(1,2); CREATE TABLE t2 ( ticket int(11), inhalt text, KEY tig (ticket), fulltext index tix (inhalt) ); INSERT INTO t2 VALUES (1,'foo'),(2,'bar'),(3,'foobar'); select t1.id FROM t2 as ttxt,t1,t1 as ticket2 WHERE ticket2.id = ttxt.ticket AND t1.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar'); id select t1.id FROM t2 as ttxt,t1 INNER JOIN t1 as ticket2 ON ticket2.id = ttxt.ticket WHERE t1.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar'); id INSERT INTO t1 VALUES (3,3); select t1.id FROM t2 as ttxt,t1 INNER JOIN t1 as ticket2 ON ticket2.id = ttxt.ticket WHERE t1.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar'); id 3 show keys from t2; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t2 1 tig 1 ticket A NULL NULL NULL YES BTREE t2 1 tix 1 inhalt A NULL 1 NULL YES FULLTEXT show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `ticket` int(11) default NULL, `inhalt` text character set latin1, KEY `tig` (`ticket`), FULLTEXT KEY `tix` (`inhalt`) ) TYPE=MyISAM select * from t2 where MATCH inhalt AGAINST (NULL); ticket inhalt select * from t2 where MATCH inhalt AGAINST ('foobar'); ticket inhalt 3 foobar select * from t2 having MATCH inhalt AGAINST ('foobar'); ticket inhalt 3 foobar CREATE TABLE t3 ( ticket int(11), inhalt text, KEY tig (ticket), fulltext index tix (inhalt) ); select * from t2 where MATCH inhalt AGAINST (t2.inhalt); Wrong arguments to AGAINST select * from t2 where MATCH ticket AGAINST ('foobar'); Can't find FULLTEXT index matching the column list select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar'); Wrong arguments to MATCH drop table t1,t2,t3; CREATE TABLE t1 ( id int(11) auto_increment, title varchar(100) default '', PRIMARY KEY (id), KEY ind5 (title), FULLTEXT KEY FT1 (title) ) TYPE=MyISAM; insert into t1 (title) values ('this is a test'); select * from t1 where match title against ('test' in boolean mode); id title 1 this is a test update t1 set title='this is A test' where id=1; check table t1; Table Op Msg_type Msg_text test.t1 check status OK update t1 set title='this test once revealed a bug' where id=1; select * from t1; id title 1 this test once revealed a bug update t1 set title=NULL where id=1; drop table t1; CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) TYPE=MyISAM; insert into t1 values (1,"I wonder why the fulltext index doesnt work?"); SELECT * from t1 where MATCH (b) AGAINST ('apples'); a b insert into t1 values (2,"fullaaa fullzzz"); select * from t1 where match b against ('full*' in boolean mode); a b 2 fullaaa fullzzz 1 I wonder why the fulltext index doesnt work? drop table t1;