MATCH AGAINST|V4.4.2|OceanBase Database| docs|Distributed Database

Purpose

This expression is used for text retrieval on a full-text index. The AGAINST clause accepts a search string and searches the index based on the collation. For each row in the table, the return value of MATCH represents the relevance between the search string and the data in the row, i.e., the similarity between the text in the search string and the text in the data table.

Syntax

MATCH (column_set) AGAINST (query_expr [mode_flag])

column_set:
    column_name [, column_name ...]

mode_flag:
    IN NATURAL LANGUAGE MODE
    | IN BOOLEAN MODE
    | IN MATCH PHRASE MODE

Parameters

Field	Description
column_set	Specifies the column to be searched. If multiple columns are specified, they must be separated by commas. The query is independent of the order of the columns. To execute the `MATCH AGAINST` expression, there must be a full-text index that exactly matches the `column_set`.
query_expr	Specifies the keyword or phrase to be searched, which is the parameter to be matched. OceanBase Database will tokenize `query_expr` using the tokenizer of the full-text index and then perform a search on the full-text index.
mode_flag	Optional. Specifies the search mode. The default value is `IN NATURAL LANGUAGE MODE`. For more information, see mode_flag.

mode_flag

IN NATURAL LANGUAGE MODE: The default value. Specifies to use the natural language search mode. In this mode, the full-text search is performed by tokenizing the query expression (query_expr) to obtain a set of tokens (token) and matching these tokens with those in the index. A match is considered successful if at least one token matches. The relevance of the matched rows is ranked using the Okapi BM25 method.

By default, or if IN NATURAL LANGUAGE MODE is specified, MATCH AGAINST will use the NATURAL LANGUAGE mode for full-text search. In this mode, AGAINST accepts a search string and searches the index based on the collation. For each row in the table, the return value of MATCH represents the relevance between the search string and the data in the row, i.e., the similarity between the text in the search string and the text in the data table.
IN BOOLEAN MODE: Specifies to use the Boolean search mode. This mode supports three commonly used Boolean operators and nested operations, as follows:
- +: Represents AND, which is the intersection.
- -: Represents the negation, which is the difference.
- No operator: When used alone, it represents OR, which is the union. For example, A B means A OR B. Using the operator with the symbol will increase the relevance of the sentence but will lose the OR semantics. For example, +A B means that A must be present, and the relevance between A and B in the sentence is calculated.
- (): Represents nested operations. If there is no symbol outside the parentheses, it has the OR semantics. For example, +A (nested clause) means that A must be present or the nested clause must be present.
  
  Note
  
  For OceanBase Database V4.3.5, support for IN BOOLEAN MODE was introduced starting from V4.3.5 BP1.
  
  Here are some examples:
  1. Output sentences must contain the word "computer".
```
SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer" IN BOOLEAN MODE);
```
  2. Output sentences must contain the word "computer" and must not contain the word "weather".
```
SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer -weather" IN BOOLEAN MODE);
```
  3. Output sentences must contain the word "computer", and those containing "oceanbase" are more relevant.
```
SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer oceanbase" IN BOOLEAN MODE);
```
IN MATCH PHRASE MODE: Supports exact phrase matching.

Note

For OceanBase Database V4.4.0 and later, support for IN MATCH PHRASE MODE is available.

Here are some examples:
1. Create a table named test and add a full-text index to the specified column.
```
CREATE TABLE test(c1 int, c2 VARCHAR(200), c3 TEXT, FULLTEXT INDEX fts_idx (c2, c3));
```
2. Query records in the c2 and c3 columns that exactly match some words.
```
SELECT c2 FROM test WHERE MATCH(c2,c3) AGAINST('some words' IN MATCH PHRASE MODE);
```

Considerations

The MATCH AGAINST expression, in addition to indicating text matching, contains other semantics when it appears in different clauses of SQL:

Projection of relevance:
- Full-text search supports ranking based on term frequency, which can be represented by the projection of relevance through the MATCH AGAINST expression.
- Relevance indicates the degree of match between the matched data rows and the query (Query) in MATCH AGAINST.
- Relevance is a DOUBLE type data greater than or equal to 0. A value of 0 indicates that the data row is unrelated to the query (Query), and a higher value indicates a stronger relevance.
Filtering semantics:
- When the MATCH AGAINST expression appears in the WHERE clause and is connected to all other filtering conditions using AND, it contains filtering semantics, indicating that it will filter out data rows that do not match.
  
  Example:
```
SELECT id, digest, detail
FROM t1
WHERE MATCH (detail) AGAINST ('oceanbase');
```
  Equivalent to:
```
SELECT id, digest, detail
FROM t1
WHERE MATCH (detail) AGAINST ('oceanbase') > 0;
```
Sorting semantics:
- When the MATCH AGAINST expression appears in the WHERE clause and is connected to other filtering conditions using AND, it contains sorting semantics.
- It indicates that the results of the full-text search will be sorted in descending order based on the relevance ranking of the MATCH AGAINST expression.
- When there are multiple MATCH AGAINST expressions, the sorting will be based on the relevance of the first MATCH AGAINST expression.
Example:
```
SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance
FROM t1
WHERE MATCH (detail) against ('oceanbase');
```
Equivalent to:
```
SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance
FROM t1
WHERE MATCH (detail) AGAINST ('oceanbase')
ORDER BY relevance DESC;
```

Execution method of text retrieval

When the MATCH AGAINST expression contains filtering semantics in the SQL statement, OceanBase Database can execute the query by scanning the full-text index to calculate the MATCH AGAINST expression and then accessing the table.
When the MATCH AGAINST expression does not contain filtering semantics in the SQL statement, OceanBase Database supports scanning and filtering using other secondary indexes and accessing the full-text index based on the primary key to calculate the MATCH AGAINST expression.
When the WHERE clause of the SQL statement contains MATCH AGAINST and multiple filtering conditions connected by AND, and other secondary indexes can be hit, OceanBase Database will try to select the most cost-effective index for scanning.
When calculating the MATCH AGAINST expression by scanning the full-text index, if the query contains LIMIT, OceanBase Database will try to push the top-k calculation down to the full-text index scan to improve performance.
When the query contains MATCH AGAINST predicates connected by OR, an Index Merge plan will be automatically generated. Whether to choose the Index Merge plan depends on the cost competition result.
Note
- Starting from V4.4.1, the Index Merge plan supports multiple predicates connected by AND. When the query contains multiple predicates, including MATCH AGAINST, an Index Merge plan will be automatically generated.
- In versions earlier than V4.4.1, the Index Merge plan only supports multiple predicates connected by OR.

Examples

Create the sample table test_tbl1, and create the full-text indexes ft_idx1_test_tbl1 and ft_idx1_test_tbl1, and the index idx_test_tbl1.

CREATE TABLE test_tbl1(id INT, ref_no INT, digest VARCHAR(512), detail VARCHAR(4096),
    FULLTEXT INDEX ft_idx1_test_tbl1(detail),
    FULLTEXT INDEX ft_idx2_test_tbl1(digest, detail),
    INDEX idx_test_tbl1 (id));

Insert test data into the test_tbl1 table.

INSERT INTO test_tbl1 VALUES
    (1, 1234, 'fulltext', 'Try text retrieval with OceanBase fulltext index'),
    (2, 2345, 'log', 'OceanBase can halp with log analysis'),
    (3, 3456, 'order', 'Simple text retrieval scan will return result set in order of descending ranking in OceanBase'),
    (4, 4567, 'ranking', 'OceanBase will ranking relevance to query for matched result set'),
    (5, 5678, 'filter', 'Using text retrieval as a filter condition would be probably more efficient');

Project relevance using the MATCH AGAINST expression.

SELECT id, digest, detail, MATCH (detail) AGAINST ('oceanbase') AS relevance
FROM test_tbl1;