I am trying to get the books whose title contains what the user enters.
Assuming I have the following books:
- Trees
- The airplanes
- once upon a time
Assuming the user entered the letter a
, I previously solved it using the following query:
SELECT title
FROM Books
WHERE title LIKE "%a%";
Which returned all the books.
Then, doing some research, 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 sentence a
. I tried unsuccessfully with the query:
SELECT title
FROM Books
WHERE MATCH (title) AGAINST ('*a*' IN BOOLEAN MODE);
Is there any way to make it return the books when the title contains the phrase, either exactly or not?
From already thank you very much.
It
MATCH
AGAINST
makes searches very similar to a web search engine, that is, it searches for complete words, and not for their content. Now, using for example'a*'
si is allowed since it is used to search for words that start with A, el'*a*'
is not. In that case if you have to use the traditionalLIKE
.The function searches for "full-text"
MATCH ( ) AGAINST ( )
matches and can indeed be faster than a search using the operator , as long as certain conditions are met. Primarily that there is an index of type on the column(s) that contain the text elements that represent the universe of searches. When using the mode there is no need for a type index to exist but lookups will be slower. Indexes can only be created on MyISAM tables up to version 5.5 and as of version 5.6 they are already allowed to be created on InnoDB tables.LIKE
FULLTEXT
IN BOOLEAN MODE
FULLTEXT
FULLTEXT
Full-text searches, on the other hand, have restrictions on the minimum length of words that are considered valid controlled by the configuration parameter
ft_min_word_len
which defaults to 4 characters. If the value of this parameter is changed, the indices of type must be rebuiltFULLTEXT
. There is also a list of words that are ignored in full-text searches known as stop words .Now, in the modality,
IN BOOLEAN MODE
the asterisk can only go at the end of a string of characters and means that records containing words that begin with the prefix indicated before the asterisk are included in the result. In other wordsAGAINST('a*' IN BOOLEAN MODE)
it is similar toLIKE 'a%'
. In this case, the minimum length parameter 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 match factor that would place the records that contain the text most similar to the condition at the beginning. However, this requires creating an indexFULLTEXT
on the columns you're going to use for lookups, with the restrictions I mentioned above.The modality
IN BOOLEAN MODE
offers operators that you can use to indicate the treatment of the 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 it more weight.<
Indicates that if the text contains the word it gives it less weight.~
Indicates that the weight of the word is negative, it works as a-
smooth, which does not eliminate the match.*
Unlike the other operators, this one only goes at the end to denote a prefix.()
Parentheses are used to group conditions.""
Quoted text is searched for literally, only exact matches are included.More information in the reference documentation .
What you should do is that the fields you want to perform a search
MATCH()
andAGAINST()
change them toFULLTEXT
.For example, if I have a table called
producto
and its fields areid_producto
,nombre_producto
andprecio_producto
, what you should do is, when you have created it within MySQL, do the following:Applying the previous line, the field
nombre_producto
would already be ready to useMATCH()
andAGAINST()
.You can use an if structure so if it's a word use like and if it's a phrase match..
Cheers