Purpose
This expression is used for text retrieval on a full-text index. AGAINST accepts a search string and performs a character-set-based search in the index. For each row of data in the table, the return value of MATCH indicates the relevance between the search string and the data in that row, that is, how similar the text in the search string is to 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 |
Note |
|---|---|
| column_set | Specify the columns for the full-text search. To list multiple columns, separate them with commas. The query order does not matter; however, there must be at least one indexed column that matches the search criteria. column_set Only a full-text index that matches exactly can execute the corresponding MATCH AGAINST Expression. |
| query_expr | Specify the keyword or phrase to search for, which is the parameter to match. OceanBase AI Database will use the tokenizer of the matched full-text index to process it. query_expr After tokenization, the search is performed on the full-text index. |
| mode_flag | Optional. Specifies the full-text search mode. Default value: IN NATURAL LANGUAGE MODE. For more information, see mode_flag. |
mode_flag
IN NATURAL LANGUAGE MODE: The default value, which specifies to use the natural language search mode. In this mode, full-text search works by tokenizing the query expression (query_expr) into a set of tokens (token) and matching them against the tokens in the index. A match is considered successful if any token matches a token in the query expression. Relevance ranking (sorting) of the matched rows is performed using a frequency-based method (Okapi BM25).By default or when the
IN NATURAL LANGUAGE MODEidentifier is specified,MATCH AGAINSTuses theNATURAL LANGUAGEmode for full-text search. InNATURAL LANGUAGEmode,AGAINSTaccepts a search string and performs a character-set-based search in the index. For each row of data in the table, the return value ofMATCHindicates the relevance between the search string and the data in that row, that is, how similar the text in the search string is to the text in the data table.IN BOOLEAN MODE: Specifies to use the boolean mode for search. The current version supports three common boolean operators and nested operations, as follows:+: RepresentsAND, the intersection.-: Represents negation, the difference.The absence of an operator: When used alone, it represents
OR, the union. For example,A BmeansA OR B. Mixing the operators with the+sign will increase the relevance of existing sentences but lose the semantics ofOR. For example,+A Bmeans A must be present, and then calculates the relevance of A and B in the sentence.(): Represents nested operations. When no outer operator is present, it has the semantics ofOR. For example,+A (nested clause)means A must be present or the nested clause must be true.Examples:
The output sentence must contain "computer".
SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer" IN BOOLEAN MODE);The output sentence must contain "computer" and must not contain "weather".
SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer -weather" IN BOOLEAN MODE);The output sentence must contain "computer", and "oceanbase" makes the match more relevant.
SELECT * FROM my_table WHERE MATCH (doc) AGAINST ("+computer oceanbase" IN BOOLEAN MODE);
IN MATCH PHRASE MODE: Supports exact phrase matching.Example:
Create a table named
testand 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));Query for records in columns c2 and c3 that exactly match
some words.SELECT c2 FROM test WHERE MATCH(c2,c3) AGAINST('some words' IN MATCH PHRASE MODE);
Usage instructions
In addition to representing text matching, the MATCH AGAINST expression also carries different semantics when used in various clauses of an SQL statement:
Projection correlation:
- Full-text search supports word-frequency-based ranking, which can be represented by a MATCH AGAINST expression to project relevance.
- The correlation indicates the degree of relevance between the matched data rows and the query specified by MATCH AGAINST.
- Correlation is a
DOUBLEtype data greater than or equal to 0. A value of 0 indicates that the data row has no correlation with the query. Larger values indicate stronger correlation.
Filter semantics:
For a
MATCH AGAINSTexpression that appears in theWHEREclause and is connected to all other filter conditions byAND, it contains filtering semantics, meaning it filters out data rows that do not match.Example:
SELECT id, digest, detail FROM t1 WHERE MATCH (detail) AGAINST ('oceanbase');is equivalent to
SELECT id, digest, detail FROM t1 WHERE MATCH (detail) AGAINST ('oceanbase') > 0;
Sorting semantics:
- For
MATCH AGAINSTexpressions concatenated with other filter conditions usingANDin theWHEREclause, sorting semantics are involved. - indicates that the results of the full-text search will be sorted in descending order according to the
MATCH AGAINSTranking. - When multiple
MATCH AGAINSTexpressions exist, they are sorted by the relevance of the firstMATCH AGAINSTexpression.
Example:
SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance FROM t1 WHERE MATCH (detail) against ('oceanbase');is equivalent to
SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance FROM t1 WHERE MATCH (detail) AGAINST ('oceanbase') ORDER BY relevance DESC;- For
How text retrieval works
When the
MATCH AGAINSTclause in an SQL statement contains filtering semantics, OceanBase AI Database can execute the query by scanning the full-text index to evaluate theMATCH AGAINSTexpression and then returning to the table for further processing.When the
MATCH AGAINSTclause in an SQL statement does not contain filtering semantics, OceanBase AI Database supports scanning and filtering using other secondary indexes, and calculates theMATCH AGAINSTexpression by randomly accessing the full-text index based on the primary key to execute the query.When the
WHEREclause of an SQL statement contains bothMATCH AGAINSTand multiple filter conditions connected byAND, and other secondary indexes can be matched, OceanBase AI Database attempts to select an appropriate index for scanning based on cost.When calculating
MATCH AGAINSTby scanning a full-text index, if the query contains aLIMITclause, OceanBase AI Database attempts to push down the top-k computation to the full-text index scan for better performance.When querying a
MATCH AGAINSTclause withORconnections, the system automatically attempts to generate an index merge plan. Whether to use this plan ultimately depends on the cost comparison results.Note
An index merge plan supports multiple predicates connected by
AND. An index merge plan is automatically generated when a query contains multiple predicates, includingMATCH AGAINST.
Examples
Create the sample table
test_tbl1, along with the full-text indexesft_idx1_test_tbl1andft_idx1_test_tbl1, and the indexidx_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_tbl1table.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 correlations using the
MATCH AGAINSTexpression.SELECT id, digest, detail, MATCH (detail) AGAINST ('oceanbase') AS relevance FROM test_tbl1;The return result is as follows:
+------+----------+-----------------------------------------------------------------------------------------------+--------------------+ | id | digest | detail | relevance | +------+----------+-----------------------------------------------------------------------------------------------+--------------------+ | 1 | fulltext | Try text retrieval with OceanBase fulltext index | 0.2989130434782609 | | 2 | log | OceanBase can halp with log analysis | 0.3142857142857143 | | 3 | order | Simple text retrieval scan will return result set in order of descending ranking in OceanBase | 0.240174672489083 | | 4 | ranking | OceanBase will ranking relevance to query for matched result set | 0.2849740932642488 | | 5 | filter | Using text retrieval as a filter condition would be probably more efficient | 0 | +------+----------+-----------------------------------------------------------------------------------------------+--------------------+ 5 rows in setFilter data using the
MATCH AGAINSTexpression.SELECT id, digest, detail FROM test_tbl1 WHERE MATCH (detail) AGAINST ('oceanbase');The return result is as follows:
+------+----------+-----------------------------------------------------------------------------------------------+ | id | digest | detail | +------+----------+-----------------------------------------------------------------------------------------------+ | 2 | log | OceanBase can halp with log analysis | | 1 | fulltext | Try text retrieval with OceanBase fulltext index | | 4 | ranking | OceanBase will ranking relevance to query for matched result set | | 3 | order | Simple text retrieval scan will return result set in order of descending ranking in OceanBase | +------+----------+-----------------------------------------------------------------------------------------------+ 4 rows in setRetrieve and sort data using the
MATCH AGAINSTexpression.SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance FROM test_tbl1 WHERE MATCH (detail) AGAINST ('oceanbase');or
SELECT id, digest, MATCH (detail) AGAINST ('oceanbase') AS relevance FROM test_tbl1 WHERE MATCH (detail) AGAINST ('oceanbase') ORDER BY relevance DESC;The return result is as follows:
+------+----------+--------------------+ | id | digest | relevance | +------+----------+--------------------+ | 2 | log | 0.3142857142857143 | | 1 | fulltext | 0.2989130434782609 | | 4 | ranking | 0.2849740932642488 | | 3 | order | 0.240174672489083 | +------+----------+--------------------+ 4 rows in set
Text search execution plan example
MATCH AGAINSTcontains filtering semantics and scans via the full-text indexft_idx1_test_tbl1.EXPLAIN SELECT id, digest, detail FROM test_tbl1 WHERE MATCH (detail) AGAINST ('oceanbase') AND id = 3;The return result is as follows:
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ============================================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------------------------------- | | |0 |SORT | |1 |30 | | | |1 |└─TEXT RETRIEVAL SCAN|test_tbl1(ft_idx1_test_tbl1)|1 |30 | | | ============================================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([test_tbl1.id], [test_tbl1.digest], [test_tbl1.detail]), filter(nil), rowset=16 | | sort_keys([MATCH(test_tbl1.detail) AGAINST('oceanbase'), DESC]) | | 1 - output([test_tbl1.detail], [test_tbl1.id], [test_tbl1.digest], [MATCH(test_tbl1.detail) AGAINST('oceanbase')]), filter([test_tbl1.id = 3]), rowset=16 | | access([test_tbl1.__pk_increment], [test_tbl1.detail], [test_tbl1.id], [test_tbl1.digest]), partitions(p0) | | is_index_back=true, is_global_index=false, filter_before_indexback[false], | | calc_relevance=true, match_expr(MATCH(test_tbl1.detail) AGAINST('oceanbase')), | | pushdown_match_filter(MATCH(test_tbl1.detail) AGAINST('oceanbase')) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------+ 15 rows in setMATCH AGAINSTcontains filtering semantics, but since the regular secondary indexidx_test_tbl1is matched and its scan cost is lower, the query scans throughidx_test_tbl1.EXPLAIN SELECT id FROM test_tbl1 WHERE MATCH (detail) AGAINST ('oceanbase simple text retreival scan on fulltext index') AND id = 3;The return result is as follows:
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ====================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------------------------- | | |0 |SORT | |1 |103 | | | |1 |└─TABLE RANGE SCAN|test_tbl1(idx_test_tbl1)|1 |103 | | | ====================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([test_tbl1.id]), filter(nil), rowset=16 | | sort_keys([MATCH(test_tbl1.detail) AGAINST('oceanbase simple text retreival scan on fulltext index'), DESC]) | | 1 - output([test_tbl1.id], [MATCH(test_tbl1.detail) AGAINST('oceanbase simple text retreival scan on fulltext index')]), filter([MATCH(test_tbl1.detail) | | AGAINST('oceanbase simple text retreival scan on fulltext index')]), rowset=16 | | access([test_tbl1.__pk_increment], [test_tbl1.id]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([test_tbl1.id], [test_tbl1.__pk_increment]), range(3,MIN ; 3,MAX), | | range_cond([test_tbl1.id = 3]), has_functional_lookup=true | +-------------------------------------------------------------------------------------------------------------------------------------------------------------+ 16 rows in setThe
MATCH AGAINSTclause does not support filtering semantics. It cannot filter the result set or perform a full-text index scan; instead, it scans the primary table.EXPLAIN SELECT id, digest, detail FROM test_tbl1 WHERE MATCH (detail) AGAINST ('oceanbase') OR id = 3;The return result is as follows:
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ==================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------- | | |0 |TABLE FULL SCAN|test_tbl1|5 |503 | | | ==================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([test_tbl1.id], [test_tbl1.digest], [test_tbl1.detail]), filter([MATCH(test_tbl1.detail) AGAINST('oceanbase') OR test_tbl1.id = 3]), rowset=16 | | access([test_tbl1.__pk_increment], [test_tbl1.detail], [test_tbl1.id], [test_tbl1.digest]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([test_tbl1.__pk_increment]), range(MIN ; MAX)always true, has_functional_lookup=true | +-------------------------------------------------------------------------------------------------------------------------------------------------------------+ 11 rows in setMATCH AGAINSTdoes not contain filtering semantics, so it cannot filter the result set or perform a full-text index scan. It hits indexidx_test_tbl1and scans through indexidx_test_tbl1.EXPLAIN SELECT id FROM test_tbl1 WHERE MATCH (detail) AGAINST ('oceanbase') OR id = 3;The return result is as follows:
+------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------+ | =================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------------------- | | |0 |TABLE FULL SCAN|test_tbl1(idx_test_tbl1)|5 |503 | | | =================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([test_tbl1.id]), filter([MATCH(test_tbl1.detail) AGAINST('oceanbase') OR test_tbl1.id = 3]), rowset=16 | | access([test_tbl1.__pk_increment], [test_tbl1.id]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([test_tbl1.id], [test_tbl1.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true, has_functional_lookup=true | +------------------------------------------------------------------------------------------------------------------------------+ 11 rows in setMATCH AGAINSTcontains filtering semantics, causing the scan to hit the corresponding full-text indexft_idx1_test_tbl1, whileLIMITis used to push down the top-k index scan.EXPLAIN SELECT id, digest, detail FROM test_tbl1 WHERE MATCH (detail) AGAINST ('oceanbase') LIMIT 3;The return result is as follows:
+------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------+ | =========================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | --------------------------------------------------------------------------- | | |0 |TEXT RETRIEVAL SCAN|test_tbl1(ft_idx1_test_tbl1)|4 |31 | | | =========================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([test_tbl1.id], [test_tbl1.digest], [test_tbl1.detail]), filter(nil), rowset=16 | | access([test_tbl1.__pk_increment], [test_tbl1.detail], [test_tbl1.id], [test_tbl1.digest]), partitions(p0) | | is_index_back=true, is_global_index=false, | | calc_relevance=true, match_expr(MATCH(test_tbl1.detail) AGAINST('oceanbase')), | | pushdown_match_filter(MATCH(test_tbl1.detail) AGAINST('oceanbase')), | | sort_keys([MATCH(test_tbl1.detail) AGAINST('oceanbase'), DESC]), limit(3), offset(nil), with_ties(false) | +------------------------------------------------------------------------------------------------------------------+ 13 rows in set