BM25

For columnstore tables created with a FULLTEXT index, the text columns in that table can be searched using BM25. The BM25 search provides partition-level scoring which improves accuracy by scoring all rows within a partition together. Collection and term statistics are calculated within a partition, ensuring accurate scores relative to a row's placement within that partition.

In contrast, queries using the MATCH syntax are scored on a segment level. As partitions contain multiple segments, searches using BM25 return more accurate results than MATCH, but may be less efficient. SingleStore recommends using BM25 when accuracy of results is important.

The result of the BM25 function is a relevancy score. A higher score indicates a higher quality match, while a lower score indicates a lower quality match. There is no upper bound to a BM25 score; the score is intended to be used for ordering and is not intended to be directly interpreted.

Important

To include recent inserts/updates from in the results, run OPTIMIZE TABLE tbl_name FLUSH before running your query.

Refer to Working with Full-Text Search for conceptual information on full-text search.

Syntax

The syntax for BM25 scoring is as follows:

SQL

BM25(<table_name>, <expression>)

The USING VERSION 2 syntax must be used in the CREATE TABLE command to utilize BM25. Refer to Working with Full-Text Search for more information on VERSION 2.

Specify multiple BM25 clauses to search against multiple tables. Additionally, BM25 full-text search works best over English text and is case-insensitive.

Operators

BM25 supports a set of operators from Java Lucene full-text search string syntax. The <expression> consists of a list of one or more columns, followed by a mix of text with zero or more of the following operators.

Operator	Description
(no operator)	When no operator is specified, the word is optional; however, the rows that contain it are rated higher.
+	A leading plus sign indicates that this word must be present in each row returned.
-	A leading minus sign indicates that this word must not be present in any of the rows that are returned. Note: The `-` operator acts only to exclude rows that are otherwise matched by other search terms.
NOT	The `NOT` operator behaves the same as the `-` operator. The symbol `!` can be used in place of the word `NOT`. The `NOT` operator must be in all caps.
AND	The `AND` operator matches documents where both terms exist anywhere in the text of a single document. This is equivalent to an intersection using sets. The symbol `&&` can be used in place of the word `AND`. The expression A AND B is equivalent to +A +B. The `AND` operator must be in all caps.
OR	The `OR` operator behaves the same as not having any operator between words. The symbol \|\| can be used in place of the word `OR`. The `OR` operator must be in all caps.
()	Parentheses group words into subexpressions. Parenthesized groups can be nested.
""	A phrase is a quoted string with two or more space-delimited terms. For example, `'key:"word1 word2"'` is correct syntax for a phrase. The double quotes around the phrase `"word1 word2"` are required. The words in the phrase search may not use wildcards or proximity search notation. A phrase that is enclosed within double quote (") characters matches the words in the quotes as if it is a single word. If the phrase contains no words that are in the index, the result is empty. The words might not be in the index because of a combination of factors: if they do not exist in the text, are stopwords, or are shorter than the minimum length of indexed words.
~<proximity>	A `~` with a `<proximity>` value can be appended to the `BM25` query when using a phrase. The proximity is the maximum number of words allowed between terms in the phrase. For example, `"word1 word2"~3` would be an example of appending a proximity value of `3` to the phrase `"word1 word2"`.

Stopwords

Certain words are ignored by full-text search due to their commonality resulting in less relevant results. These are called stopwords. SingleStore’s default list of stopwords is as follows:

a, an, and, are, as, at, be, but, by, for, if, in, into, is, it, no, not, 
of, on, or, such, that, the, their, then, there, these, they, this, to, was, will, with

Special Characters

Depending on the use case, FULLTEXT matching may not always be compatible with match expressions involving special characters. This is because the default tokenizer for SingleStore’s FULLTEXT removes special characters from the search. The current list special characters are:

+ - && || ! ( ) { } [ ] ^ " ~ * ? : \

Relevancy Score

The relevancy score of an expression in a BM25 statement denotes the ranking of the expression based on the following factors:

Number of times an expression appears in a column. More occurrences of an expression in the matched column(s) increases its relevancy score.
Rarity of the expression. Rare words have a higher relevancy score than commonly used words.
The length of the column containing the expression. A column with a short expression has a higher relevancy score than a column with a long expression.

Remarks

The column specification in the <expression> may contain a JSON keypath. A keypath is an expression containing one or more keys, delimited by dots, that describes the hierarchical path to a field in a JSON document. For example, key1.key2.key3 would be a valid keypath.
Refer to MATCH for more examples of full-text search with the above operators.

Examples

Create Example Data

Create a table with a FULLTEXT VERSION 2 index on columns title and body. Insert data into that table and optimize the table to ensure queries have access to all the added rows via the fulltext index.

Either column (title or body) can be queried separately using the BM25 function, and the index on the column will be applied.

SQL

CREATE TABLE articles (
    id INT UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    SORT KEY (id),    
    FULLTEXT USING VERSION 2 art_ft_index (title, body));

SQL

INSERT INTO articles (id, year, title, body) VALUES
   (1, 2021, 'Introduction to SQL', 'SQL is a standard language for accessing and manipulating databases.'),
   (2, 2022, 'Advanced SQL Techniques', 'Explore advanced techniques and functions in SQL for better data manipulation.'),
   (3, 2020, 'Database Optimization', 'Learn about various optimization techniques to improve database performance.'),
   (4, 2023, 'SQL in Web Development', 'Discover how SQL is used in web development to interact with databases.'),
   (5, 2019, 'Data Security in SQL', 'An overview of best practices for securing data in SQL databases.'),
   (6, 2021, 'SQL and Data Analysis', 'Using SQL for effective data analysis and reporting.'),
   (7, 2022, 'Introduction to Database Design', 'Fundamentals of designing a robust and scalable database.'),
   (8, 2020, 'SQL Performance Tuning', 'Tips and techniques for tuning SQL queries for better performance.'),
   (9, 2023, 'Using SQL with Python', 'Integrating SQL with Python for data science and automation tasks.'),
   (10, 2019, 'NoSQL vs SQL', 'A comparison of NoSQL and SQL databases and their use cases.'),
   (11, 2020, 'Real-time Data Analysis', 'An introduction to real-time analytics.'),
   (12, 2021, 'Analysis for Beginners', 'Simple examples of real time analytics.'),
   (13, 2023, 'Data-Dictionary Design', 'Create and maintain effective data dictionaries.'),
   (14, 2024, 'Scalable Performance', 'Designing for scalability.');

OPTIMIZE TABLE articles FLUSH;

Example 1 - Search for a Term

Search for articles with the word 'database' in the body. This query will return rows where the BM25 score for the row is non-zero.

SQL

SELECT id, title, body, BM25(articles,'body:database') AS score
   FROM articles
   WHERE BM25(articles,'body:database');

+------+---------------------------------+------------------------------------------------------------------------------+---------------------+
| id   | title                           | body                                                                         | score               |
+------+---------------------------------+------------------------------------------------------------------------------+---------------------+
|    7 | Introduction to Database Design | Fundamentals of designing a robust and scalable database.                    | 0.13076457381248474 |
|    3 | Database Optimization           | Learn about various optimization techniques to improve database performance. |  0.3219999074935913 |
+------+---------------------------------+------------------------------------------------------------------------------+---------------------+

Example 2 - Use BM25 Twice in one Query

SQL

SELECT title
    FROM articles  
   WHERE BM25(articles, 'title:SQL OR body:("Business Intelligence")')
  AND BM25(articles,'body:web');

+------------------------+
| title                  |
+------------------------+
| SQL in Web Development |
+------------------------+

Example 3 - Use BM25 with a SQL Predicate

SQL

SELECT count(*)
  FROM articles
  WHERE year = 2021
 AND BM25(articles,'body:SQL');

+----------+
| count(*) |
+----------+
|        2 |
+----------+

Example 4 - Score over Multiple Columns

Search for the word 'database' across two columns

SQL

SELECT *
FROM articles
  WHERE BM25(articles,'title:database OR body:database' );

+------+------+---------------------------------+------------------------------------------------------------------------------+
| id   | year | title                           | body                                                                         |
+------+------+---------------------------------+------------------------------------------------------------------------------+
|    7 | 2022 | Introduction to Database Design | Fundamentals of designing a robust and scalable database.                    |
|    3 | 2020 | Database Optimization           | Learn about various optimization techniques to improve database performance. |
+------+------+---------------------------------+------------------------------------------------------------------------------+

Example 5 - Use a Single Boolean Operator

Boolean operators allow terms to be combined through logic operators. The following Boolean operators are supported: AND, "+", OR, NOT and "-"

Note

Boolean operators must be in ALL CAPS.

SQL

SELECT title
  FROM articles
 WHERE BM25(articles,'title:Database OR title:"Business Intelligence"');

+---------------------------------+
| title                           |
+---------------------------------+
| Introduction to Database Design |
| Database Optimization           |
+---------------------------------+

Example 6 - Use Multiple Boolean Operators

The following example returns the title of an article that contains either 'Database' or 'Business Intelligence' in the title and the string 'real-time analytics' in the body.

SQL

SELECT title
  FROM articles
  WHERE BM25(articles,'(title:Database OR title:"Analysis") AND (body:"real-time analytics")');

+-------------------------+
| title                   |
+-------------------------+
| Analysis for Beginners  |
| Real-time Data Analysis |
+-------------------------+

Example 7 - Score over JSON

For this and the following example, create a table named scoring with a FULLTEXT VERSION 2 index on columns b and c. Insert data into that table and optimize the table to ensure queries have access to all the added rows via the fulltext index.

SQL

CREATE TABLE scoring (a INT, b TEXT, c JSON, SORT KEY(a),
                        FULLTEXT USING VERSION 2 scr_ft_index (b, c));

INSERT INTO scoring VALUES
  (1, 'one two three three four four four five five five five five','{"one": "two"}');
INSERT INTO scoring VALUES
  (2, 'one two two three three three three four four four four four four four four','{"one": "three"}');

OPTIMIZE TABLE scoring FLUSH;

To obtain BM25 scores over JSON documents at a keypath, use the following query. In this query, the keypath is one:three.

SQL

SELECT a, ROUND(BM25(scoring, 'c$one:three'), 3) AS score_one 
FROM scoring;

+-----+-----------+
| a   | score_one |
+-----+-----------+
| 1   |     0.000 | 
| 2   |     0.131 |
+-----+-----------+

Example 8 - Use Phrases and Proximity Search

Proximity search enables you to find words within a specific distance of each other. To do a proximity search use the tilde, ~, symbol with a <proximity> value at the end of a phrase. For example, to search for a "Single" and "Store" within 10 words of each other in a row use the search: "Single Store"~10.

The following example finds BM25 scores over phrases using proximity search. Observe the change in scores as the proximity value is increased.

SQL

SELECT a
     , ROUND(BM25(scoring,'b:"two four"'), 3) AS score_two_four
     , ROUND(BM25(scoring,'b:"two four"~2'), 3) AS score_two_four_proximity_2
     , ROUND(BM25(scoring,'b:"two four"~4'), 3) AS score_two_four_proximity_4
FROM scoring;

+-----+----------------+----------------------------|----------------------------+ 
| a   | score_two_four | score_two_four_proximity_2 | score_two_four_proximity_4 |
+-----+----------------+----------------------------|----------------------------+
| 1   |          0.000 |                     0.125  |                     0.125  |
| 2   |          0.000 |                     0.000  |                     0.082  |
|+----+----------------+----------------------------+----------------------------+

BM25

On this page

Syntax

Operators

Stopwords

Special Characters

Relevancy Score

Remarks

Examples

Create Example Data

Example 1 - Search for a Term

Example 2 - Use BM25 Twice in one Query

Example 3 - Use BM25 with a SQL Predicate

Example 4 - Score over Multiple Columns

Example 5 - Use a Single Boolean Operator

Example 6 - Use Multiple Boolean Operators

Example 7 - Score over JSON

Example 8 - Use Phrases and Proximity Search

Was this article helpful?

On this page

Was this article helpful?

BM25

On this page

Syntax

Operators

Stopwords

Special Characters

Relevancy Score

Remarks

Examples

Create Example Data

Example 1 - Search for a Term

Example 2 - Use BM25 Twice in one Query

Example 3 - Use BM25 with a SQL Predicate

Example 4 - Score over Multiple Columns

Example 5 - Use a Single Boolean Operator

Example 6 - Use Multiple Boolean Operators

Example 7 - Score over JSON

Example 8 - Use Phrases and Proximity Search

Related Topics

Was this article helpful?

On this page

Was this article helpful?