Rank

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.

Linking Bias

Danah Boyd posted about the biases of links over at Many2Many the other day. She looked for patterns in a random set of 500 blogs tracked by Technorati as well as the 100 top blogs tracked by Technorati. She found patterns in who keeps blogrolls and who is in them, as well as patterns about how bloggers link in context and who they link to.

The patterns Boyd points to would certainly effect the Google Economy, our way of creating and identifying value based on linking structures. And though she’s emphasizing gender differences, the patterns show broad differences in linking patterns between content types as well.

Discussion?