Full-Text Search Functions
editFull-Text Search Functions
editThis functionality is in beta and is subject to change. The design and code is less mature than official GA features and is being provided as-is with no warranties. Beta features are not subject to the support SLA of official GA features.
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.
SCORE
editSynopsis:
SCORE()
Input: none
Output: double numeric value
Description:Returns the relevance of a given input to the executed query. The higher score, the more relevant the data.
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