MySQL LIKE operator Vs MATCH AGAINST

The fastest solution is to create a properly normalized table for tags, so that each tag is stored on a separate row.

CREATE TABLE tags (
  tag VARCHAR(4),
  tableid INT,
  PRIMARY KEY (tag, tableid),
  KEY (tableid, tag)
);

SELECT * FROM `table` JOIN tags ON table.tableid = tags.tableid 
WHERE tags.tag IN ('1111', '2222', '3333');

Benefits:

  • No more worrying about fulltext indexes, ft_min_length, InnoDB support, etc.
  • No more worrying about the bad performance of substring matching with LIKE.
  • Looking up a given tag and its matching entries in table uses the primary key index.
  • Looking up the set of tags for a given entry in table uses the secondary key index.
  • You have no limit to the number of tags per item in table.
  • You can easily count the frequency of certain tags, you can fetch the set of distinct tags, you can constrain tags against a lookup table, etc.

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*.