You are viewing an older version of this section. View current production version.
MATCH
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. The result of the MATCH statement is a relevancy score between zero and one indicating the quality of the match.
The columns specified in a MATCH clause must be from the same table. To search against multiple tables, you need to specify multiple MATCH clauses. Also, full text search is works best over English text.
If you need to include recent small inserts/updates from the hidden rowstore table in with your results, you should run OPTIMIZE TABLE tbl_name FLUSH before running your query.
See Full Text Search for more conceptual information on this feature.
Syntax
MATCH (col1,col2,...) AGAINST (expression)
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. 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. |
| *, ? | 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. 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. |
| ~ | The tilde symbol is used to support fuzzy searches. To do a fuzzy search, use the tilde symbol at the end of a single-word term. For example, to search for a term similar in spelling to “roam” use the fuzzy search: roam~. |
Range searches and boosting terms to increase relevancy are not supported.
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. For example, to search for “text” or “test” you can use the search:
te?t
Multiple character wildcard searches looks for zero or more characters. For example, to search for test, tests or tester, you can use the search:
test*
You can also use the wildcard searches in the middle of a term.
te*t
You cannot use a * or ? symbol as the first character of a search.
Escaping special characters
The current list special characters are:
+ - && || ! ( ) { } [ ] ^ " ~ * ? : \
To escape these characters, use \ before the character.
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,
KEY (id) USING CLUSTERED COLUMNSTORE,
FULLTEXT (title,body));
SELECT * FROM articles
WHERE 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,
KEY (id) USING CLUSTERED COLUMNSTORE,
FULLTEXT(body));
SELECT count(*)
FROM books
WHERE publish_year = 2017 AND MATCH (body) AGAINST ('memsql');
The following example searches for either the word “database” or “business intelligence”:
SELECT title
FROM articles
WHERE 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:
SELECT title
FROM articles
WHERE MATCH (title) AGAINST ('Database OR "Business Intelligence"')
AND MATCH(body) AGAINST ("real-time analytics");
This example uses the relevance score as an output column:
SELECT id, title, MATCH(body) AGAINST ('database') relevance
FROM articles
WHERE MATCH(body) AGAINST ('database')
This example returns matches greater than a specific relevance score:
SELECT id, title, MATCH(body) AGAINST ('database')
FROM articles
WHERE 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');
