Get fields that contain a phrase with Match () Against ()

Navigation
2

I'm trying to get the books whose titles contain what the user enters.

Assuming I have the following books:

  • Trees
  • Planes
  • There was once trus

Assuming that the user entered the letter a , before resolving it using the following query: 

SELECT title 
    FROM Books 
    WHERE title LIKE "%a%";

Which returned all the books to me.

Then, investigating, I found that the function Match Against is much faster than like so I tried to migrate it, but to my surprise when I do the query: 

SELECT title 
    FROM Books 
    WHERE MATCH (title) AGAINST ('a' IN BOOLEAN MODE);

It does not return anything, since it does not contain the complete phrase a . I tried without success with the query: 

SELECT title 
    FROM Books 
    WHERE MATCH (title) AGAINST ('*a*' IN BOOLEAN MODE);

Is there a way to get the books back when the title contains the phrase, either exactly or not?

Thank you very much already.

    
asked by Genarito 24.02.2017 в 04:44
source

3 answers

3

The MATCH AGAINST searches very similar to a web browser, that is, searches for complete words, and not for their content. Now, use for example 'a*' if it is allowed since it is used to look for words that begin with A, the '*a*' is not. In that case if you have to use the traditional LIKE .

    
answered by 24.02.2017 / 14:14
source
1

The MATCH ( ) AGAINST ( ) function searches for "full text" (full-text) matches and can be faster than a search using the LIKE operator, as long as certain conditions are met . Primarily that there is an index of type FULLTEXT in the column (s) that contain the text elements that represent the universe of the searches. When the IN BOOLEAN MODE mode is used, it is not necessary to have an index of type FULLTEXT but searches will be slower. The indexes FULLTEXT can only be created in MyISAM tables up to version 5.5 and from version 5.6 it is already allowed to be created in InnoDB tables.

On the other hand, full-text searches have restrictions on the minimum length of words that are considered valid controlled by the configuration parameter ft_min_word_len whose default value is 4 characters. If the value of this parameter is changed, indexes of type FULLTEXT must be reconstructed. There is also a list of words that are ignored in full-text searches known in English as < em> stop words .

Now, in the IN BOOLEAN MODE mode the asterisk can only go to the end of a string of characters and means that records containing words beginning with the prefix indicated above are included in the result of the asterisk. In other words AGAINST('a*' IN BOOLEAN MODE) is similar to LIKE 'a%' . In this case, the parameter of the minimum length does not matter since the prefix does not indicate a complete word, but only a prefix.

It seems to me that the functionality you are looking for would be to use the natural language mode AGAINST('texto tecleado por el usuario' IN NATURAL LANGUAGE MODE) , which would generate a result ordered by a coincidence factor that would place the records that contain the text closest to the condition at the beginning. However, this requires the creation of a FULLTEXT index in the columns you will use for searches, with the restrictions I mentioned above.

The IN BOOLEAN MODE modality offers operators that you can use to indicate the treatment of words:

  • + Indicates that the text must contain the word.
  • - Indicates that the text should NOT contain the word.
  • > Indicates that if the text contains the word it gives more weight.
  • < Indicates that if the text contains the word it gives less weight.
  • ~ Indicates that the weight of the word is negative, works as a - smooth, that does not eliminate the coincidence.
  • * Unlike the other operators, this only goes to the end to denote a prefix.
  • () Parentheses are used to group conditions.
  • "" The text between quotes is searched literally, only exact matches are included.

More information on the reference documentation .

    
answered by 24.02.2017 в 19:16
0

What you must do is that the fields you want to search for MATCH() and AGAINST() change them for FULLTEXT .

For example, if I have a table called producto and its fields are id_producto , nombre_producto and precio_producto , what you should do is, when you have it created within MySQL do the following: 

ALTER TABLE producto ADD FULLTEXT(nombre_producto)

Applying the previous line, the field nombre_producto would already be ready to use MATCH() and AGAINST() .

    
answered by 12.09.2017 в 18:46
How to use a class as a type of a function external to the class? Convert a string to char in c ++