CODEWITHSUNDEEP
mysql
BubblewrapBeast
BubblewrapBeast

Reputation: 1607

Mysql not ranking results, fulltext

I have setup a database and enabled fulltext search, i have some entries in the database that include the word 'test' and one that has 'test some more' when i used the below to search the database:

SELECT keywords, title FROM database WHERE Match(keywords) Against ('more test')

I was expecting it to rank the entry that had 'test some more' above the one that just had 'test' in it.

Am i doing something wrong maybe?

Thanks.

Upvotes: 0

Views: 28

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477686

You never specify that the results must be ordered, you simply put an additional constraint on the result that there must be a match.

You can solve this with the following query:

SELECT keywords, title,
    MATCH (keywords) AGAINST ('more test') AS relevance
FROM database
WHERE MATCH (keywords) AGAINST ('more test')
ORDER BY relevance DESC

And as @GordonLinoff mentions, you probably need to disable the stopwords filter by setting stopwords.txt variable to the empty string (''; but remember the original value to restore it).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

By default, MySQL removes certain words from the text search. These are called "stop words" and they are not in the index. You can read about them here. The words "more" and "some" are examples of stop words.

You can provide your own list of stop words, but you will have to recreate the index.

Upvotes: 0

Related Questions