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