eharetea

Using fulltext searches in MySQL.

Create searchable data!

Date Created:Friday December 29th, 2006 03:41 AM
Date Modified:Friday August 01st, 2008 01:17 AM

NOTE:
VARCHAR, CHAR, TEXT columns inside MyISAM tables can have FULLTEXT indexes.

#on create:
CREATE TABLE comments (
comment_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
subject VARCHAR(100) NOT NULL,
comment TEXT NOT NULL,
PRIMARY KEY (comment_id),
FULLTEXT (comment) );

mysql> create table comments (
    -> comment_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    -> link_type char(32) NOT NULL,
    -> link_id INT UNSIGNED NOT NULL,
    -> short_desc TEXT NOT NULL,
    -> long_desc TEXT NOT NULL,
    -> PRIMARY KEY(comment_id),
    -> FULLTEXT(long_desc) );
Query OK, 0 rows affected (0.05 sec)

#using alter:
ALTER comment ADD FULLTEXT INDEX(comments);

# return the relevance:
SELECT short_desc
MATCH(long_desc)
AGAINST('pattern') AS Relevance
FROM comments
WHERE MATCH(long_desc) AGAINST('pattern');