Full-Text Searching in MySQL

Album Cover: Vitalogy

"Wait for signs, believe in lies, to get by, it's divine."
Pearl Jam / Tremor Christ

Posted on August 24, 2005 6:45 PM in Web Development
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

I've been meaning to upgrade my search page for quite some time now. The main reason being that my old algorithm was case-sensitive (uber annoying) and not even vaguely robust. This was due to the fact that I was simply plugging in the search term(s) to a LIKE query. Typically, MySQL queries are case-insensitive by default, but such is not the case when you are performing a LIKE query on a binary column of a type such as BLOB or TEXT.

Fortunately enough, in finally getting around to updating my search page I was able to take advantage of MySQL's relatively new full-text search functions. This is a really cool new feature that has been discussed in detail elsewhere.

Before I could implement full-text searching here on my blog, though, I had to tackle one minor caveat. Way back in the day, I made the mistake of defining the data column that contains all of my full blog posts as a BLOB rather than TEXT, and because of slight differences in the ways those data types are stored by MySQL, full-text searching is only available to columns defined as the latter. Luckily, switching from BLOB to TEXT was easy as pie (and I imagine the inverse is true). The task was accomplished with the following query:

ALTER TABLE blog MODIFY text TEXT;

Now that my data types were in compliance with the requirements for full-text searching, all I had to do was make the following alteration to my table of blog entries:

ALTER TABLE blog ADD FULLTEXT(title, text);

By doing so, I had let MySQL know that I planned on performing full-text search queries that involved those two columns. Now, my search page utilizes this new functionality via a search similar to the following example:

SELECT id, title FROM blog WHERE MATCH(title, text) AGAINST('firefox');

This has made my search page much more robust because not only is it no longer sensitive to case, but it also sorts the search results by relevance automatically. Pretty cool, huh?

I realize this probably comes as old news to a lot of techheads out there, but it's always exciting to see cool technology (even if it is a bit dated) applied to something so close to your heart (or brain, maybe?).

Look for mo' betta search capabilities here very soon.

Comments

No one has added any comments.

Post Comments

If you feel like commenting on the above item, use the form below. Your email address will be used for personal contact reasons only, and will not be shown on this website.

Name:

Email Address:

Website:

Comments:

Check this box if you hate spam.