MySQL LIKE statement Vs MATCH AGAINST
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 types 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 and accurate and which is faster?
thank
source to share
Your searches are not equivalent. LIKE %1%
will find ANYTHING that contains 1
eg. 100
, 911
, 0.1
. It's just a regular substring. MATCH ('+1')
will work in theory, but FULLTEXT by default ignores any "words" up to 4 characters long. However, if you relax the full text length limitation, +1
any INDEPENDENT will find 1
, but not the one embedded in another word. For this you will need +*1*
.
source to share
The fastest solution is to create a properly normalized table for tags so that each tag is stored on a separate line.
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 full-text indexes, ft_min_length, InnoDB support, etc.
- No longer worry about poor performance of a substring matching
LIKE
. - Searching for a specific tag and its matching records in
table
uses the primary key index. - When searching for a set of tags for a given record
table
, the secondary key index is used. - You have no limit on the number of tags per item in
table
. - You can easily count the frequency of certain tags, you can get a set of different tags, you can restrict tags from the lookup table, etc.
source to share