Peter Gulutzan, author of SQL Performance Tuning, writes in The Full-Text Stuff That We Didn’t Put In The Manual about the particulars of word boundaries, index structure, boolean searching, exact phrase searching, and stopwords, as well as offering a few articles for further reading (Ian Gilfillan’s “Using Fulltext Index in MySQL”, Sergei Golubchik’s “MySQL Fulltext Search”, Joe Stump’s “MySQL FULLTEXT Searching”). It’s one of a number of articles in the MySQL Tech Resources collection.
Doing Relevance Ranked Full-Text Searches In MySQL
I’m going out on a limb to say MySQL’s full-text indexing and searching features are underused. They appeared in MySQL 3.23.23 (most people are using 4.x, and 5 is in development), but it’s been news to most of the people I know.
Here’s the deal, the MATCH() function can search a full-text index for a string of text (one or more words) and return relevance-ranked results. It’s at the core of the list of related links at the bottom of every post here.
For that query, I put all the tag names into a single variable that might look like this:
$keywords = “mysql database php select full-text search full-text searching docs documentation”
Then I do a select that looks something like this:
SELECT * FROM wp_posts WHERE MATCH(post_title,post_content) AGAINST(‘$keywords’);
The docs give a lot more detail, including how to do boolean searches.