Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Searching with MySQL (and REGEXPs)

by gav^ (Curate)
on Apr 02, 2002 at 01:32 UTC ( #155901=perlmeditation: print w/ replies, xml ) Need Help??

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.

gav^

Comment on Searching with MySQL (and REGEXPs)
Select or Download Code
Re: Searching with MySQL (and REGEXPs)
by Zaxo (Archbishop) on Apr 02, 2002 at 01:54 UTC

    Have you considered Text::Balanced for the quote parsing?

    After Compline,
    Zaxo

Re: Searching with MySQL (and REGEXPs)
by perrin (Chancellor) on Apr 02, 2002 at 02:52 UTC
      MySQL's fulltext isn't too useful (to me anyway) before version 4 as you can't change the minimum word length to something bigger than 4 without a recompile. It also doesn't like '-' which means for this client you couldn't search for part numbers.

      I didn't go for one of the modules on CPAN as I wanted something simple, the text search is in addition to 4 combo boxes (finish, material, mfg, and size in this case) and most users either leave it blank or just enter 1 or 2 words. I've got keys on the important fields and searching through 5,000 records takes < 0.2 seconds for a complicated query with 5 LIKEs and 2 REGEXPs.

      I don't think this will scale fantastically for larger data sets where using some kind of fulltext index will really pay off.

      Just for kicks I ran a quick test on a table with 550,000 rows, a simple LIKE/REGEXP takes 3.5 seconds while a REGEXP takes 12.

      gav^

        Recompiling MySQL doesn't sound like a big deal to me, and there has to be a way to sneak around the "-" limitation (convert it to a letter sequence?), but you may have found the sweet spot for satisfying this requirement. If it's fast enough then you're done, and you know you can always do one of these things later.

        Inverted index searches do scale very well, since they are basically just one hash lookup per keyword being searched. I've sometimes taken advantage of this speed by encoding fields like mfg and material as special keywords. It scales well for a large data set.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://155901]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (13)
As of 2014-07-13 15:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (250 votes), past polls