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.