0

I like to sort a BOOLEAN wildcard search by relevance. As BOOLEAN has only a binary score we need to use the NATURAL mode to obtain a weight score. But NATURAL does not support operators so it will not return any results (score = 0):

mysql>SELECT
        topic_id,
        MATCH(topic_text) AGAINST('tunin') AS score
    FROM
        topics_search
    WHERE
        MATCH(topic_text) AGAINST('tunin*' IN BOOLEAN MODE)
    ORDER BY
        score DESC
    LIMIT 10
+----------+----------+
| topic_id | score    |
+----------+----------+
| 2        | 0        |
| 6        | 0        |
| 16       | 0        |
| 17       | 0        |
| 18       | 0        |
| 24       | 0        |
| 26       | 0        |
| 27       | 0        |
| 31       | 0        |
| 32       | 0        |
+----------+----------+
10 rows in set (202 ms)

I could use LIKE '%tunin%' but this won't give me a score.

Question
Is it possible to obtain all the words that are part of the fulltext index, that were hit through the wildcard search?

Example:

mysql>SELECT
        fulltext_index_words
    FROM
        topics_search
    WHERE
        MATCH(topic_text) AGAINST('tunin*' IN BOOLEAN MODE)
+----------------------+
| fulltext_index_words |
+----------------------+
| tuning               |
| tunings              |
+----------------------+
2 rows in set (1 ms)
Community
  • 1
  • 1
mgutt
  • 5,867
  • 2
  • 50
  • 77
  • This behaviour is very specific to the combination of MyISAM with MySQL < 5.7, so you might just upgrade or switch to InnoDB (if you aren't already there anyway, the linked question is 5 1/2 years old). If you can't, you might want to explain your problem, because it might be easier to write your own weight function than to force something onto the fulltext search (which might not even be what you think you get). But to answer your question: no, there is no function that will give you that words, but you could probably write one (e.g. by going through the input string character by character). – Solarflare Feb 08 '17 at 02:22
  • Nice. Didn't know that 5.7 returns scores: https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html Post your comment as answer with quoting this link source and I will accept it. – mgutt Feb 08 '17 at 09:34
  • No, it's added in 5.6: https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html – mgutt Feb 08 '17 at 09:42
  • P.S. "just upgrade" is not possible in my environment. We use the stable Debian 8. After Debian 9 is released I hope we can benefit from MySQL 5.6. – mgutt Feb 08 '17 at 11:48

0 Answers0