Re: Re: Searching with MySQL (and REGEXPs)

[gav^]

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^

[perrin]

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.