MySQL Full Text Search

Today I played to optimize a slow SQL request type

SELECT * FROM 'table' WHERE `field` LIKE '%word%'

Where is the troublesome one moment here – the last part word% ‘%’ and in even greater concreteness characters % before the word, for that we do. Wildcard symbol % ,before any value, our application directly translates directly into a slow, because in this way the application stops us to use the indexes of the. As always there are solutions, but are not always clear 😆 generally MySQL you have a solution to this problem with fulltext search Indexing of the. How is the change of the field there is a lot written in the documentation, but in a hurry I will describe how I change the top request, because we will get to a little drama finally. Make a cute as applicable to fulltext field up, the query needs to be changed in the form:

SELECT * FROM `table` WHERE MATCH (field) AGAINST ('word')

So the structure is obvious and there is no need for unnecessary discussion. The above query will enter into force, If the word, for which you are making a request at least 4 the symbol, This is the default value, If you want to modify it, you must specify the value, you want to my.cnf in part [mysqld] with the Declaration ft_min_word_len= 3 or 2, 1 not a good choice obviously 😉 . After you change the value and restart the mysql server-need to do a repair of tables, in order for the new indexing enters into force. Here all clear: do you change, I reset, rebildvam indices and make my request and returns 0 Checking line with 😀

SHOW VARIABLES

I see that the values, I've asked have entered into force, rebildvam indexes again – same result. 🙄 Hate, very uncomfortable. From here on began a large cursing and scratching the key to the shed 😀 who was quite, pretty interesting. Generally, I started reading documentation do not know which road and came to an interesting passage

Such a technique works best with large collections (in fact, it was carefully tuned this way). For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results. For example, although the word "MySQL" is present in every row of the articles table shown earlier, a search for the word produces no results

BEAM 😳 Dame tray I was small – It was still a test. In a large application Naših table with over 2 000 000 order and there things are asleep. Well it's already clear the problem. To make it clear the solution, I will mention briefly, that full text search support 3 Advanced modes BOOLEAN , EXPRESSIONS and NATURAL LANGUAGE as the last works by default. About the different modes you can check the documentation, I'll explain with 2-3 words to BOOLEAN because it is the key. It supports logical operators of the type AND, OR , NOT and so on and can make some magic with the search phrases, to have a, is there another, etc.. Supports and symbol *, that is the equivalent of wildcard characters % 😉 It is useful, When the search term is in the length of the ft_min_word_len or small trays ;). At least to me at the table with about 100 the line is doing a perfect job. The only thing left to see and the finished application:

SELECT * FROM `table` WHERE MATCH (field)
AGAINST ('*word*' IN BOOLEAN MODE)

Now here comes a time indexing works with us wildcard characters – the answer is I don't know. Basically I, to, because otherwise indicated in the documentation, but the documentation is clearly not say or show much 😀

Enhanced by Zemanta

2 comments

leave a reply

Your email address will not be published. Required fields are marked *

anti SPAM *