Fulltext frustration

One of the most fun projects I’d taken on in the last year or so was a search function using the Porter Stemmer algorithm and a MySQL fulltext search.  What the what?

Lets start at the beginning.  MySQL has a nifty feature called fulltext, which basically indexes text fields (one, two, all of them, text or varchar) which can then be searched using a match query that will match up your search term with bits and pieces in those indexed text fields.  So lets say you have a field that contains a description for an item.  You want your end users to be able to search through that description, and you don’t want to strictly rely on a set of keywords.  That’s where fulltext comes in handy.  This could also be accomplished by the ‘LIKE’ comparison, but fulltext also offers the ability to return the result’s relevancy (as calculated by some mysterious set of numbers I won’t go in to today).

The Porter Stemmer Algorithm is a handy tool for cutting words off at the stem, so you don’t need to have all sorts of variations of a word in order for a search to be effective.  An example would be, in your table you have a field that contains the keyword ‘respect’.  What if your end user types in respectable, respectability, or respectful?  Now in some cases, you don’t want this to match, perhaps the particular word is not a good one.  But in some cases you would (if it’s part of a description or something similar).  What the stemmer algorithm does, in short, it cuts off the three example search words down to the stem ‘respect’ which then of course matches a word in your description, and yay, you have a match in your search.

So, we put these all together, and we have a matching fulltext search using the stem of a word.  This works great for months, years, and then all of a sudden stops working.  I have a client whose search did just this.  I spit out the coldfusion query using the result keyword in the cfquery(<cfdump var=”#theResult#”>, so I knew exactly what the query was that was run, the query, the cfqueryparams,  the recordcount and so on.  In the mysql query window, it works fine.  In coldfusion, it doesn’t.  <tears hair out>  I don’t think I’ve been this frustrated about code in a while (usually it’s people).   If I find a solution, I’ll be sure to let all of my 2 readers know.