LIKE or REGEXP or MATCH on "text" fields

[Craig Matthews]

Hi,

It is inevitable to have some SQL where LIKE is vital, e.g., when searching through a field for many words.

Let's say I have a column of the type TEXT or MEDIUMTEXT. I want to allow users to search through this field for keyword(s).

Which is the best way to search for strings:

1. LIKE -- in case of more than one keywords this also degenerates into an OR situation so most likely the worst scenario.

2. REGEXP -- with the > 1 keywords all separated by a pipe.

3. MATCH -- full text search.

Thanks for any thoughts.