MATCH
On this page
For tables created with a FULLTEXT index, the columnstore columns in that index can be searched through using the MATCH AGAINST
syntax.
MATCH
takes a comma-separated list of column names and AGAINST
takes an expression to filter the data.MATCH
statement is a relevancy score between 0 and 1 indicating the quality of the match.
The columns specified in a MATCH
clause must be from the same table.MATCH
clauses.
Important
If you need to include recent small inserts/updates from the hidden rowstore table in with your results, you should run OPTIMIZE TABLE tbl_
before running your query.
See Full Text Search for more conceptual information on this feature.
Syntax
MATCH (<column1>,<column2>,...) AGAINST (<expression>)
Operators
The AGAINST
expression consists of 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. |
NOT |
The |
AND |
The |
OR |
The |
() |
Parentheses group words into subexpressions. |
*, ? |
See the Wildcard support section below. |
"" |
A phrase that is enclosed within double quote (") characters matches the words in the quotes as if it is a single word. |
~ |
The tilde symbol is used to support fuzzy searches. |
Important
Range searches and boosting terms to increase relevancy are not supported.
Note
SingleStore supports only constant expressions (search filters) inside the AGAINST
clause, for example, a regular expression like (Stock*)
or a constant string like "DBC".
Stopwords
Certain words are ignored by full-text search due to their commonality resulting in less relevant results.
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
Wildcard Support
Single and multiple character wildcard searches within single terms are supported (not within phrase queries).
To perform a single character wildcard search use the ?
symbol and to perform a multiple character wildcard search use the *
symbol.
The single character wildcard search looks for terms that match that with the single character replaced.text
or test
you can use the search: te?t
Multiple character wildcard searches looks for zero or more characters.test*
You can also use the wildcard searches in the middle of a term.te*t
Important
You cannot use a *
or ?
symbol as the first character of a search.
Special Characters
Depending on the use case, FULLTEXT
matching may not always be compatible with match expressions involving special characters.FULLTEXT
removes special characters from the search.
+ - && || ! ( ) { } [ ] ^ " ~ * ? : \
Warning
To escape these characters, use \\ before the character.
The exception to this rule is that commas used to separate numbers like 1,000
or 12345,6789,12345
will not a have a negative effect on a search.
Relevancy Score
The relevancy score of an expression in a MATCH
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.
Examples
The following example searches for the word database
across two columns:
CREATE TABLE articles (id INT UNSIGNED,year int UNSIGNED,title VARCHAR(200),body TEXT,SORT KEY (id),FULLTEXT (title, body));SELECT * FROM articlesWHERE MATCH (title,body)AGAINST ('database');
The following example combines MATCH
with a SQL predicate:
CREATE TABLE books(id INT UNSIGNED,name VARCHAR(100),publish_year INT UNSIGNED,body TEXT,SORT KEY (id),FULLTEXT(body));SELECT count(*)FROM booksWHERE publish_year = 2017 AND MATCH (body) AGAINST ('memsql');
The following example searches for either the word database
or business intelligence
:
SELECT titleFROM articlesWHERE MATCH (title) AGAINST ('Database OR "Business Intelligence"');
The following example returns the title of an article that contains either database
or Business Intelligence
and the string real-time analytics
in the body.real-time
is not escaped in order to include results for both real time
and real-time.
SELECT titleFROM articlesWHERE MATCH (title) AGAINST ('Database OR "Business Intelligence"')AND MATCH(body) AGAINST ("real-time analytics");
This example shows a search for any id with the naming convention id-articleX, where X is the ID number.id-article1
or id-article227,
but it will not return id-227.
The special character is escaped here to avoid results including a space, since this violates the convention for assigning ids to each article.CREATE TABLE
statement above.
SELECT titleFROM articlesWHERE MATCH(article_id) AGAINST ('"id\\-" +article*');
This example uses the wildcard ’*’ to return all articles with titles starting with the word Journal
such as Journalism
, Journalist
, Journals
, and so on.
SELECT *FROM articlesWHERE MATCH(title) AGAINST ('Journal*');
This example returns the articles where the title starts with Data
and also contains the word function
.Database function
and Database functions
.
SELECT titleFROM articlesWHERE MATCH (title) AGAINST ('+Data* AND function?');
In the following example, the special character -
is escaped to include all results starting with Data-
, followed by words starting with di
.Data-directory
, Data-dictionary
, and so on.
SELECT * FROM articles WHERE MATCH(title) AGAINST ('+Data\\- +di*');
This example uses the relevance score as an output column:
SELECT id, title, MATCH(body) AGAINST ('database') relevanceFROM articlesWHERE MATCH(body) AGAINST ('database')
This example returns matches greater than a specific relevance score:
SELECT id, title, MATCH(body) AGAINST ('database')FROM articlesWHERE MATCH(body) AGAINST ('database') > .12;
MATCH
can also be used with UPDATE
or DELETE
queries.
UPDATE articles set name = concat(name,".DATABASE") where MATCH(body) AGAINST ('database');DELETE from articles where MATCH(body) AGAINST ('database');
Related Topics
Last modified: February 24, 2023