FULLTEXT and LIKE

From: Date: Thu, 22 Jul 2004 18:57:16 +0000
Subject: FULLTEXT and LIKE
Groups: php.db 
Request: Send a blank email to php-db+get-35435@lists.php.net to get a copy of this message
Hi, My head is swimming with lots of info gleaned from various newsgroups about using fulltext indexes in MySQL, but, I'm still not sure what the best solution would be for what I want to do. I'm concerned about the 3-char minimum and not being able to search on partial words (my version of MySQL doesn't support boolean fulltext searches). I have a simple News table that is structured like this: CREATE TABLE news ( id int(7) unsigned NOT NULL auto_increment, author varchar(50) NOT NULL default '', headline varchar(60) NOT NULL default '', content text NOT NULL, url text NOT NULL, UNIQUE KEY id (id), ) TYPE=MyISAM; I want searches for News to be on the author, headline and content fields. What is the best way to index and search this? A) Create a combined fulltext index: FULLTEXT (author,headline,content) -- and do searches using MATCH() and AGAINST(). B) Create a fulltext index only for the content field and use this kind of select: SELECT * FROM db WHERE author LIKE "%phrase%" OR headline LIKE "%phrase%" OR MATCH(content) AGAINST(phrase); C) None of the above ... is there a better way? Thanks a lot for any input or feedback! I'm completely lost! Monty

« previous php.db (#35435) next »