MATCH AGAINST|V4.3.5| docs|Distributed Database

MATCH AGAINST

Last Updated：2025-11-14 07:33:32 Updated

Purpose

This expression is used to perform text retrieval on full-text indexes. The AGAINST clause takes a search string as its parameter, which is compared with the index in the specified character set. The MATCH function returns a value that represents the relevance between the search string and the data in each row of the table. The relevance value indicates 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

Parameters

Parameter	Description
column_set	Specifies the columns to be searched. If you want to specify multiple columns, separate them with commas. The order of the columns does not matter. A full-text index that exactly matches the `column_set` must exist for the corresponding `MATCH AGAINST` expression to be executed.
query_expr	Specifies the keywords or phrases to be searched, which is the parameter to be matched. OceanBase Database will tokenize the `query_expr` based on the tokenizer used for the full-text index and then perform a search on the full-text index.
mode_flag	Optional. Specifies the full-text 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, which specifies the use of natural language search mode. In this mode, the system tokenizes the query expression (query_expr) into tokens (token) and matches these tokens with those in the index. A match is considered successful if any token in the query expression matches a token in the index. The system also ranks the matched rows based on their relevance to the query expression using the Okapi BM25 algorithm.

If the IN NATURAL LANGUAGE MODE keyword is not explicitly specified, MATCH AGAINST defaults to using the NATURAL LANGUAGE mode for full-text search. In this mode, the AGAINST clause accepts a search string as its parameter and compares it with the index according to the specified character set. For each row in the table, the MATCH function returns a relevance score that indicates the similarity between the search string and the text in the row.
IN BOOLEAN MODE: specifies the use of Boolean search mode. The current version supports three commonly used Boolean operators and nested Boolean expressions. The operators are described as follows:
- +: indicates AND, representing the intersection of sets.
- -: indicates NOT, representing the difference between sets.
- No operator: When no operator is specified, it implies OR, representing the union of sets. For example, A B is equivalent to A OR B. Using the + operator can increase the relevance of queries containing both terms, but the OR semantics are lost. For example, in the query +A B, the result must include A, and the relevance between A and B is calculated.
- (): represents nested Boolean expressions. If no operator precedes the parentheses, the OR operator is implied. For example, +A (nested clause) means that the sentence must contain A or the nested clause.
  
  Note
  
  For OceanBase Database V4.3.5, the IN BOOLEAN MODE clause was introduced starting from V4.3.5 BP1.
  
  Here is an example:
  1. The output sentence must contain the word computer.
```
SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer" IN BOOLEAN MODE);
```
  2. The output sentence 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. The output sentence must contain the word computer, with oceanbase being more relevant.
```
SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer oceanbase" IN BOOLEAN MODE);
```

Considerations

The MATCH AGAINST expression can also be used for projection, filtering, and sorting in different clauses of an SQL statement:

Projection:
- Full-text search supports ranking based on token frequency. The MATCH AGAINST expression can be used to project the relevance.
- The relevance value indicates the relevance between the matched rows and the query (Query) in the MATCH AGAINST expression.
- The relevance value is a DOUBLE value that is greater than or equal to 0. A relevance value of 0 indicates that the matched rows are irrelevant to the query (Query). The higher the relevance value, the more relevant the matched rows are to the query (Query).
Filtering:
- If the MATCH AGAINST expression in the WHERE clause is connected to other filter conditions with the AND operator, the MATCH AGAINST expression has a filtering semantics, which means that the rows that do not match the MATCH AGAINST expression will be filtered out.
  
  Here is an example:
```
SELECT id, digest, detail
FROM t1
WHERE MATCH (detail) AGAINST ('oceanbase');
```
  This statement is equivalent to:
```
SELECT id, digest, detail
FROM t1
WHERE MATCH (detail) AGAINST ('oceanbase') > 0;
```
Sorting:
- If the MATCH AGAINST expression in the WHERE clause is connected to other filter conditions with the AND operator, the MATCH AGAINST expression has a sorting semantics.
- The system sorts the full-text search results in descending order of relevance based on the MATCH AGAINST expression.
- If there are multiple MATCH AGAINST expressions, the rows are sorted in descending order of relevance based on the first MATCH AGAINST expression.
Here is an example:
```
SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance
FROM t1
WHERE MATCH (detail) against ('oceanbase');
```
This statement is equivalent to:
```
SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance
FROM t1
WHERE MATCH (detail) AGAINST ('oceanbase')
ORDER BY relevance DESC;
```

Execution methods for full-text search

If the MATCH AGAINST expression in the SQL statement has a filtering semantics, OceanBase Database will scan the full-text index to calculate the MATCH AGAINST expression and then return to the table to execute the query.
If the MATCH AGAINST expression in the SQL statement does not have a filtering semantics, OceanBase Database will scan and filter the table based on other secondary indexes and calculate the MATCH AGAINST expression based on the primary key to execute the query.
If the WHERE clause of the SQL statement contains the MATCH AGAINST expression and multiple filter conditions connected with the AND operator, and other secondary indexes can be hit, OceanBase Database will try to select an index to scan based on the cost.
When the MATCH AGAINST expression is calculated based on the full-text index scan, if the query contains a LIMIT clause, OceanBase Database will try to push the top-k calculation to the full-text index scan to improve the performance.
When querying with a MATCH AGAINST predicate that contains an OR connection, the system will automatically attempt to generate an index merge plan. Whether the index merge plan is ultimately selected depends on the result of cost-based optimization.

Note

For OceanBase Database V4.3.5, the generation of index merge plans is supported starting from V4.3.5 BP1.

Examples

Create a sample table test_tbl1, and create two full-text indexes ft_idx1_test_tbl1 and ft_idx1_test_tbl1, and an 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');

Use the MATCH AGAINST expression to project the relevance.

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