Orange is my favorite color
June 1, 2006

Web/Internet | Trackback | del.icio.us | digg

The MySQL full-text engine is pretty impressive. It’s been around for a long time (since late 3.x versions) but I haven’t had the need to use it until a small project I was working on recently for a consulting customer. The default behavior is to use an algorithm to statistically determine the most relevant matches in the database. This didn’t quite work right when someone searched for something like “Long Beach”; two words that are really a single search term. In the clients’ words, “we want it to be like Google”.

I don’t think this is Google-like exactly but it does seem to perform better for this search function. I made a few small changes to the query to use the boolean mode and force all search terms to be included:


<cfset arrKeywords = listToArray(arguments.data, " ")>
<cfloop from="1" to="#arrayLen(arrKeywords)#" index="ii">
    <cfset arrKeywords[ii] = "+" & arrKeywords[ii]>
</cfloop>
<cfset arguments.data = arrayToList(arrKeywords, " ")>

Then in the query itself, I forced boolean mode which combined with the + characters requires all search terms to be present:

AND MATCH (vchObjective, txtEvaluationText) AGAINST (#arguments.data# IN BOOLEAN MODE)

Based on limited testing, this seems to solve the issue with finding “Long Beach” versus records that contain “long”.

2 Comments

  1. Jason said:

    on June 12, 2006 at 12:30 pm

    mySQL’s full-text search has been a never-ending source of headaches for me… while it works fine in the context of mySQL’s documentation (hey, search this title for this word), its ease of implementation starts falling apart in real life if you want to, say, search more than one field in one table.

    I ran into a similar problem that you describe, but my solution was less elegant: I taught the users of the db how to use operators (such as the plus to force an include) own their own.

    Like the new design, btw. Never would’ve guessed you like the color orange (*cough* vFive *cough).

  2. brian said:

    on June 12, 2006 at 12:56 pm

    Well I can’t take credit for the design; that part I have stolen from a wordpress theme and it’s sitting in an unyet-changed state.

    It might not be perfect, but MySQLs built in searching certainly beats doing a bunch of LIKE ‘%blah%’s as far as I’m concerned.

{ RSS feed for comments on this post}