MySQL search via match() against()?

Question

MySQL

Hello!
I have a search that searches using match() against(). But the problem is that it does not give accurate results. For example, if you do a search for “cat”, it displays not cats on the front page, but what contains “Category”.
The question is simple - How do I make LIKE '% cat %' OR tags LIKE '% cat' OR tags LIKE 'cat %' in match() against()
More details:
Tags are stored as "animal cat happy" or "" "inventory categories"
Here is the sql to search compact disc

SELECT *, MATCH (tags) AGAINST('&gt;&gt;&quot;compact disc&quot; &gt;(+compact* +disc* ) &lt;(compact* disc* )' IN BOOLEAN MODE) as rel FROM icons <br/>
WHERE MATCH (tags) AGAINST('&gt;&gt;&quot;compact disc&quot; &gt;(+compact* +disc* ) &lt;(compact* disc* )' IN BOOLEAN MODE) <br/>
ORDER BY rel DESC

if you make a request through LIKE, you have to do a UNION to distribute the data by relevance (in the case of the word "cat")

(SELECT * FROM icons WHERE tags LIKE '% cat %' OR tags LIKE '% cat' OR tags LIKE 'cat %')<br/>
UNION<br/>
(SELECT * FROM icons WHERE tags LIKE '%cat%')<br/>

In this case, the result is better, but the load on the server is greater if the search is for 2 or more words.
I tried to pull the tags into a separate table and create a many-to-many relationship, but this also loads the server more than the match against option.

Answer 1

Use Sphinx. Full-text search in MySQL - the same as

Answer 2

LIKE gives a huge load on the DBMS.
It all depends on which table engine you are using. For full-text search with morphology, sphinx is the best choice.
If to do through match it is best of all MyISAM. but in general www.mysql.ru/docs/man/Fulltext_Search.html
SELECT word FROM table WHERE
MATCH (word) AGAINST ('cat*' IN BOOLEAN MODE)

Answer 3

If the string with tags in the database starts with a space and ends with a space, then three conditions will not be necessary: ​​)