Full-Text Search Functionsedit

Search functions should be used when performing full-text search, namely when the MATCH or QUERY predicates are being used. Outside a, so-called, search context, these functions will return default values such as 0 or NULL.

SCOREedit

Input: None, Output: double

Returns the relevance of a given input to the executed query. The higher score, the more relevant the data.

Note

When doing multiple text queries in the WHERE clause then, their scores will be combined using the same rules as Elasticsearch’s bool query.

Typically SCORE is used for ordering the results of a query based on their relevance:

SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY SCORE() DESC;

    SCORE()    |    author     |       name        |  page_count   |    release_date
---------------+---------------+-------------------+---------------+--------------------
2.288635       |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00Z
1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00Z
1.6086555      |Frank Herbert  |Children of Dune   |408            |1976-04-21T00:00:00Z
1.4005898      |Frank Herbert  |God Emperor of Dune|454            |1981-05-28T00:00:00Z

However, it is perfectly fine to return the score without sorting by it:

SELECT SCORE() AS score, name, release_date FROM library WHERE QUERY('dune') ORDER BY YEAR(release_date) DESC;

     score     |       name        |    release_date
---------------+-------------------+--------------------
1.4005898      |God Emperor of Dune|1981-05-28T00:00:00Z
1.6086555      |Children of Dune   |1976-04-21T00:00:00Z
1.8893257      |Dune Messiah       |1969-10-15T00:00:00Z
2.288635       |Dune               |1965-06-01T00:00:00Z