Is possible to optimize the queries?

I have a db with id, title, description, keywords as columns, I need to query and have result in relevant order.

I need to use more keywords, should I use AND or OR or to build the query in other way?

SELECT *, MATCH (title) AGAINST ('earth AND food AND money'
             IN NATURAL LANGUAGE MODE) AS SCORE 
FROM _table 
WHERE MATCH (title) AGAINST ('earth AND food AND money'
             IN NATURAL LANGUAGE MODE)

or

SELECT *, MATCH (title) AGAINST ('earth OR food OR money'
             IN NATURAL LANGUAGE MODE) AS SCORE 
FROM _table 
WHERE MATCH (title) AGAINST ('earth OR food OR money'
             IN NATURAL LANGUAGE MODE)

or?

thanks

CodePudding user response：

You need to combine the search words with the characters provided in the link

MySQL uses what is sometimes referred to as implied Boolean logic, in which

  stands for AND

- stands for NOT

[no operator] implies OR 

For more information see manual

So your first query would be

SELECT 
    *,
    MATCH (title) AGAINST (' earth  food  money' IN NATURAL LANGUAGE MODE) AS SCORE
FROM
    _videvotradotto
WHERE
    MATCH (title) AGAINST (' earth  food  money' IN NATURAL LANGUAGE MODE)

and the second

SELECT 
    *,
    MATCH (title) AGAINST ('earth food money' IN NATURAL LANGUAGE MODE) AS SCORE
FROM
    _videvotradotto
WHERE
    MATCH (title) AGAINST ('earth food money' IN NATURAL LANGUAGE MODE)

CodePudding user response：

"earthworm" and "dearth" were included in the LIKE. So `LIKE gives you extra words.

In the other direction, IN NATURAL LANGUAGE MODE runs a second query based on other words found in the first pass -- This is how the "720" is larger. Perhaps you wanted IN BOOLEAN MODE instead.

You should really look at the documents to observe both of these "anomalies", and possibly other anomalies.

Meanwhile, 'AND' and 'OR' are probably "stop words". This means that they were ignored. (One would hope that "the" is ignored in English!)

Meanwhile, nbk explains how to do AND and OR in FULLTEXT.