ONLamp.com: MySQL FULLTEXT Searching

ONLamp.com: MySQL FULLTEXT Searching


Creating the tables-- The main blog table with our FULLTEXT index
-- Nothing extreme here, but you get the idea
CREATE TABLE blog_entries
(
entryID INT(9) UNSIGNED NOT NULL DEFAULT '0' AUTO_INCREMENT,
posted INT(11) UNSIGNED NOT NULL DEFAULT '0',
categoryID TINYINT(2) UNSIGNED NOT NULL DEFAULT '0',
title CHAR(255) NOT NULL DEFAULT '',
entry TEXT NOT NULL DEFAULT '',
PRIMARY KEY (entryID),
KEY (posted),
KEY (categoryID),
FULLTEXT(title,entry)
);
-- A category table so you can organize your posts and
-- later do some fun searching based on such data.
CREATE TABLE blog_categories
(
categoryID TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AUTO_INCREMENT,
name CHAR(75) NOT NULL DEFAULT '',
PRIMARY KEY (categoryID)
);
-- Add some data into your blog
INSERT INTO blog_categories VALUES (1,'Personal');
INSERT INTO blog_categories VALUES (2,'Work');
INSERT INTO blog_categories VALUES (3,'Editorials');
INSERT INTO blog_entries
VALUES (1,
1050942000,
1,
'About miester',
'I was born in michigan in 1980 in a small town called Adrian.
My mother is named Sue, while my father is named Mike.
They currently live in a small town called East Jordan. On April
27th, 2003 I will graduate from Eastern Michigan University with a
degree in Computer Information Systems.');


mysql> SELECT entryID,title
-> FROM blog_entries
-> WHERE MATCH (title,entry) AGAINST('mother');



mysql> SELECT E.entryID, E.title, C.name
-> FROM blog_entries AS E, blog_categories AS C
-> WHERE E.categoryID=C.categoryID AND
-> MATCH (E.title, E.entry) AGAINST ('michigan') AND
-> E.categoryID=1;