This all started with one of those, "wouldn't it be nice if...?" converstations I often have with clients, the ones where they have a good idea but you really wish they told you before you got 95% of the way though. Anyway, I had a simple search script working, you could select from a couple of combo boxes and type in some text and you were set. The text search was handled pretty simply, same as I normally do. Break up the words and create some horrid SQL that uses like:
@textquery = (); $text =~ s/[^\w\d\s\-]//g; @words = split /\s+/, $text; foreach my $word (@words) { push @textquery, 'search_text LIKE ?'; push @data, '%' . $word . '%'; }
Then it's a pretty simple matter of passing things to DBI and getting some results back. Things worked nicely, the search_text column was a TEXT field that I used CONCAT to fill with the columns that were searchable (name, headline, caption, etc) which is a lot faster than trying to search on multiple columns:
UPDATE table SET search_text = CONCAT_WS(' ', name, headline, caption) +;
</code> Then the client rings. He's been trying to search for 'cat' and 'cup' and not getting the results he wanted. Ok, we'll see what we can do about that. I decide that if the user puts a word in quotes we will search for that exact word.

First we use the ever-so-useful Text::ParseWords to break things up into words and things in quotes:

$text =~ s/[^\w\d\s'"\-]//g; @words = parse_line('\s+', 1, $text);
The 1 means that parse_line will leave the quotes in place, which means we can work out which 'words' if any had quotes around them. However if the user typed in "cat's" we don't get anything back:
unless (@words) { @words = split /\s+/, $text; }
Now we want to do something like /\b$word\b/ in MySQL. Unfortunatly we can't do that with a LIKE statement as we just can't match something at the start or the end of the string with '% word %'. MySQL has some cunning tricks under its sleeves, it supports regexps! These are quite limiting compared to perl but it supports [[:<:]] and [[:>]] which are the same as a \b. The strange thing is that MySQL's regexp's are so slow (I'm running 3.23) that:
SELECT x, y, z FROM table WHERE x REGEXP '[[:<:]]word[[:>]]'
is about 4x as slow (on a table with 5000 rows) as than
SELECT x, y, z FROM table WHERE x LIKE '%word%' AND x REGEXP '[[:<:]]w +ord[[:>]]'
So all we have to do is build up our SQL as before, taking care to use REGEXP if the user put quotes around something:
foreach my $word (@words) { if ($word =~ s/^(["'])(.+)\1$/$2/) { push @textquery, 'srch_text LIKE ? AND srch_text REGEXP ? '; push @data, '%' . $word . '%'; push @data, '[[:<:]]' . $word . '[[:>:]]'; } else { push @textquery, 'srch_text LIKE ?'; push @data, '%' . $word . '%'; } }
And now the user can search for 'cat' and 'cup' to their hearts delight! The client was happy, I learnt a new trick with MySQL and the world continued to spin for a bit longer.

If anyone has any suggestions for improvements, they'd be very much appreciated.