MATCH versus LIKE?

[james120]

I am wondering what the difference between MATCH and LIKE is? If you allow extra characters on either side, it seems like the same thing. Such as MATCHing '%word%' versus LIKing 'word'.

But, I ask because MATCH works much better for a column of small text that I have (just as the manual suggests it would) which is indexed with a normal unique index (not a metamorph index). The difference is huge, so I was wondering perhaps if LIKE requires a metamorph index, so that using LIKE on a normal index is that same as not having an index at all.

[John]

Yes, LIKE will only use a metamorph index. MATCHES will use a regular index, and works particularly well if you are looking for a column starting with a value, i.e. matches 'word%'. The other difference is that LIKE 'word' respects word boundaries, whereas MATCHES '%word%' will do a substring match.

[mark]

also...
MATCHES is a simple single pattern match. LIKE is a whole text query language unto itself. See "METAMORPH INTELLIGENT QUERY LANGUAGE" in the manual. Also see the vortex manual <apicp> settings. Many/most of them are related to LIKE statements.