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.

tags: , , , , , , , , , , , , , , , , , , , , , ,

8 thoughts on “Doing Relevance Ranked Full-Text Searches In MySQL

  1. Pingback: Plesk Bites « MaisonBisson.com

  2. Pingback: Epsilon-Delta: Mathematics and Computer Programming » Dissecting MySQL Fulltext Indexing

  3. Pingback: Pulni´s private News » Volltextsuche

  4. Pingback: Is Sun’s T2000 Up To It? « MaisonBisson.com

  5. Pingback: Epsilon-Delta » Dissecting MySQL Fulltext Indexing

  6. Thanks Maison, was looking for it. I wanted to sort the results of SELECT query by releveance. But I was stuck with ‘%LIKE%’. You have helped me out.

    Thanks a lot dear!

Comments are closed.