有哪些方法可以改善这个长的mysql搜索查询？

Question

Looking to improve this mysql select statement for a search query:

Select * from table WHERE ( user = '$search_query'
OR user LIKE '$search_query %'
OR keyword LIKE '$search_query'
OR tag LIKE '$search_query'
OR tag LIKE '% $search_query'
OR tag LIKE '% $search_query%'
OR tag LIKE '$search_query%'
OR REPLACE(question, ',' ,'') LIKE '$search_query %'
OR REPLACE(question, ',' ,'') LIKE '% $search_query'
OR REPLACE(question, ',' ,'') LIKE '% $search_query %' 
OR REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(question, '\'', ''), ',', ''), '.',''), ':',''), ';',''), '!',''), '?','') LIKE '%$search_query%'
)

The reason it's broke down the way it is, is because say if someone searches for "art", I don't want it showing results for "heart" as well.

I really need to have of these same functions but running fewer resources.

Answer 1

If your table is MyISAM, you can create a FULLTEXT index on it:

CREATE FULLTEXT INDEX fx_mytable_user_tag_question ON mytable (user, tag, question)

SELECT  *
FROM    mytable
WHERE   MATCH(user, tag, question) AGAINST ('+some*' IN BOOLEAN MODE)

This query will return some and something but not awesome.

Actually, the first step (creating the index) is not required, however, it will speed up the queries and allow more complex searches (not limited to BOOLEAN MODE) and relevance ranging.

By default, minimal length of a search query is 4 characters, so art mentioned in your example would not be found.

To work around this, you will have to change parameter ft_min_word_len in server settings.