Partial String Matching

Wed, 08/08/2007 - 19:32 — Anonymous

Is there any alternative to LIKE '%...%' OR LIKE '%...%' in MySQL if you have to offer partial string matching on a large dataset?

Thu, 08/09/2007 - 17:57 — mkemp (not verified)

MySQL allows you to do regular expressions using RLIKE. I don't know how the performance compares to using LIKE though. e.g.

SELECT * FROM my_table WHERE my_field LIKE '%foo%' or my_field LIKE '%bar%';

vs.

SELECT * FROM my_table WHERE my_field RLIKE '.*(foo|bar).*';

Here's the page in their doc:

Thu, 08/09/2007 - 18:52 — Anonymous (not verified)

How would that possibly be faster? Also you might get queries like

field LIKE '%a%' AND (field LIKE '%b%' OR field LIKE '%c%')

Thu, 08/09/2007 - 18:55 — Anonymous (not verified)

I was looking for a product like Lucene but for partial string matching.

High Scalability Building bigger, faster, more reliable websites.