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 0 and 1 indicating the quality of the match. A score closer to 1 indicates a higher quality match, while a score closer to 0 indicates a lower quality 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. Additionally, full text search works best over English text and is case insensitive.

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.

Stopwords

Certain words are ignored by full text search due to their commonality resulting in less relevant results. These are called stopwords. MemSQL’s default list of stopwords is as follows:

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. 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

Special Characters

Depending on the use case, FULLTEXT matching may not always be compatible with match expressions involving special characters. This is because the default tokenizer for MemSQL’s FULLTEXT removes special characters from the search. The current list special characters are:

+ - && || ! ( ) { } [ ] ^ " ~ * ? : \

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. The special character in ‘real-time’ is not escaped in order to include results for both ‘real time’ and ‘real-time.’

SELECT title
    FROM articles  
    WHERE 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. This search will return ‘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. Note: This example requires a fulltext index on the id column rather than title, as indicated in the CREATE TABLE statement above.

SELECT title
    FROM articles  
    WHERE MATCH(article_id) AGAINST ('"id\\-" +article*');

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');

Related Topics

• HIGHLIGHT

Was this Article Helpful?

Thanks For Your Feedback!