MySQL LIKE operator Vs MATCH AGAINST

Question

Hi I'm worried about how to implement a simple search query, my scenario is:

tag VARCHAR 255

Now I need to search inside the tag field and I can use two kinds of queries:

  SELECT * FROM table WHERE tag LIKE '%1111%' OR LIKE '%2222%' OR LIKE '%3333%';

or

SELECT * ,MATCH(tag) AGAINST('+1111','+2222','+3333' IN BOOLEAN MODE) as score FROM table ORDER BY score DESC ;

Which is more accurate/precise and which is faster?

Thanks

Answer 1

Your searches aren't equivalent. LIKE %1% will find ANYTHING that contains a 1, e.g. 100, 911, 0.1. It's just a plain substring match. MATCH ('+1') would theoretically work, but FULLTEXT by default ignores any "words" that are <4 characters in length. However, assuming you relaxed the fulltext length limit, +1 would find any INDEPENDENT 1, but not any that are embedded in another word. For that you'd need +*1*.