Mysql Fulltext search

fulltextsearch.sql

# CREATE a TABLE TO USE FOR testing fulltext INDEX AND full-text search
DROP TABLE IF EXISTS test;
CREATE TABLE test (
id int UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title char(100) NOT NULL,
article text NOT NULL,
fulltext (article) # CREATE the fulltext INDEX ON FIELD article
)engine=myisam; # Fulltext INDEX only works WITH MyISAM TABLES, which IS usually DEFAULT

# A way TO CREATE a fulltext INDEX ON an already existing TABLE
# CREATE fulltext INDEX articleIndex ON test (article);

# INSERT DATA INTO TABLE
INSERT INTO test (title, article) VALUES ('Football', 'Molde has the best football team in Norway');
INSERT INTO test (title, article) VALUES ('Salmon', 'Moldeelva can offer salmon at medio 200g');
INSERT INTO test (title, article) VALUES ('Music', 'Molde has the biggest music festival in Norway');

# A normal fulltext search matches only UNIQUE strings
SELECT * FROM test WHERE match (article) against ('Molde has the best football team in Norway')

# A BOOLEAN mode search matches words IN this example
SELECT * FROM test WHERE match (article) against ('molde' IN BOOLEAN mode);

# A BOOLEAN mode search

SELECT * FROM test WHERE match (article) against ('+molde -music' IN BOOLEAN mode);