FULLTEXT searching with MySQL

Joe Stump [1,2] discusses the FULLTEXT search feature with MySQL [3,4].

SELECT ID, post_title FROM b2posts WHERE MATCH ( post_title, post_content ) AGAINST (\’sqlite\’);

CREATE TABLE b2posts (
ID int(10) unsigned NOT NULL auto_increment,
post_author int(4) NOT NULL default \’0\’,
post_date datetime NOT NULL default \’0000-00-00 00:00:00\’,
post_content text NOT NULL,
post_title text NOT NULL,
post_category int(4) NOT NULL default \’0\’,
post_karma int(11) NOT NULL default \’0\’,
PRIMARY KEY (ID),
UNIQUE KEY ID (ID),
FULLTEXT KEY title (post_title,post_content)
) TYPE=MyISAM;

An adaption on b2\’s b2posts table produces reasonable results; not investigated further though.

[1] http://www.joestump.net
[2] http://www.joestump.net/pages/articles.php/contentID,307943731
[3] http://www.onlamp.com/lpt/a/3941
[4] http://www.mysql.com/doc/en/Fulltext_Search.html

Leave a Reply