MATCH
On this page
For tables created with a FULLTEXT index, the columnstore columns in that index can be searched by using the MATCH AGAINST
syntax.
The result of the MATCH
statement is a relevancy score between 0 and 1, which indicate the quality of the match.
Important
To include recent inserts/updates from the hidden rowstore table in with the results, run OPTIMIZE TABLE tbl_
before running your query.
Refer to Working with Full-Text Search for more conceptual information on this feature.
Syntax
The full-text search version 2 MATCH
syntax is:
MATCH (TABLE <table_name>) AGAINST (<expression>)
The legacy full-text search MATCH
syntax is:
MATCH (<column1>,<column2>,...) AGAINST (<expression>)
The VERSION 2
refers to SingleStore's next generation full-text search process.USING VERSION 2
syntax must be used in the CREATE TABLE
command to utilize the full-text search version 2 process.
The columns specified in a MATCH
clause must be from the same table in the legacy full-text search process.MATCH
clauses to search against multiple tables.
Note
The legacy full-text search version is deprecated.VERSION 2
for new development of applications that use full-text search.
VERSION 2
does not allow the column names of full-text columns to contain a $
character.$
character.
Operators
Full-text search version 2 supports operators listed on the Java Lucene full-text search string syntax page.
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. |
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".AGAINST
expressions at runtime.
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
Neither ?
or *
are supported at the beginning of a term.
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
Full-Text Search Version 2 MATCH Example
This example creates a FULLTEXT
index for both the title column and the body column.MATCH (TABLE <table_
, and the index on the column will be applied.
CREATE TABLE articles (id INT UNSIGNED,year int UNSIGNED,title VARCHAR(200),body TEXT,SORT KEY (id),FULLTEXT USING VERSION 2 art_ft_index (title, body));
INSERT INTO articles (id, year, title, body) VALUES(1, 2021, 'Introduction to SQL', 'SQL is a standard language for accessing and manipulating databases.'),(2, 2022, 'Advanced SQL Techniques', 'Explore advanced techniques and functions in SQL for better data manipulation.'),(3, 2020, 'Database Optimization', 'Learn about various optimization techniques to improve database performance.'),(4, 2023, 'SQL in Web Development', 'Discover how SQL is used in web development to interact with databases.'),(5, 2019, 'Data Security in SQL', 'An overview of best practices for securing data in SQL databases.'),(6, 2021, 'SQL and Data Analysis', 'Using SQL for effective data analysis and reporting.'),(7, 2022, 'Introduction to Database Design', 'Fundamentals of designing a robust and scalable database.'),(8, 2020, 'SQL Performance Tuning', 'Tips and techniques for tuning SQL queries for better performance.'),(9, 2023, 'Using SQL with Python', 'Integrating SQL with Python for data science and automation tasks.'),(10, 2019, 'NoSQL vs SQL', 'A comparison of NoSQL and SQL databases and their use cases.');OPTIMIZE TABLE articles FLUSH;
SELECT *FROM articlesWHERE MATCH (TABLE articles) AGAINST ('body:database');
+----+------+---------------------------------+-----------------------------------------------------------------------------+
| id | year | title | body |
+----+------+---------------------------------+-----------------------------------------------------------------------------+
| 7 | 2022 | Introduction to Database Design | Fundamentals of designing a robust and scalable database. |
| 3 | 2020 | Database Optimization | Learn about various optimization techniques to improve database performance.|
+----+------+---------------------------------+-----------------------------------------------------------------------------+
SELECT titleFROM articlesWHERE MATCH (TABLE articles) AGAINST ('title:SQL OR body:("Business Intelligence")')AND MATCH (TABLE articles) AGAINST ('body:web+');
+------------------------+
| title |
+------------------------+
| SQL in Web Development |
+------------------------+
SELECT titleFROM articlesWHERE MATCH (TABLE articles) AGAINST ('title:(+Data*) OR title:function?');
+---------------------------------+
| title |
+---------------------------------+
| Introduction to Database Design |
| SQL and Data Analysis |
| Database Optimization |
| Data Security in SQL |
+---------------------------------+
Full-Text Search Version 2 MATCH with JSON Example
A full-text index can be created over a JSON column in the same manner it can be created over any other text-type column.
CREATE TABLE ft_records (id INT UNSIGNED,title VARCHAR(200),records JSON,SORT KEY(id),FULLTEXT USING VERSION 2 rec_ft_index (title, records));
INSERT INTO ft_records VALUES (1,'document','{"k1": "cucumber","k2": ["dragonfruit", "eggplant"],"k3": [{"k3_1": "fig", "k3_2": "grape"},{"k3_1": ["huckleberry", "iceberg lettuce"]},"jicama"]}');OPTIMIZE TABLE ft_records FLUSH;
Querying over the entire JSON column can be performed in the same way as with any other column part of the full-text index.
SELECT (MATCH (TABLE ft_records) AGAINST ('records:/.*cumber/')) AS cumberFROM ft_records;
+----------+
| cumber |
+----------+
| 1 |
+----------+
To query over a specific JSON keypath, use a query similar to the following:
SELECT (MATCH (TABLE ft_records) AGAINST ('records$k3.k3_1:fig')) AS figFROM ft_records;
+-------------------------+
| fig |
+-------------------------+
| 0.13076457381248474 |
+-------------------------+
The example above shows how you can search for the string fig
at the keypath k3.
in the records
column using the field grouping syntax.
SELECT idFROM ft_recordsWHERE MATCH (TABLE ft_records) AGAINST ('records:(iceberg lettuce)');
+------+
| id |
+------+
| 1 |
+------+
SELECT idFROM ft_recordsWHERE MATCH (TABLE ft_records) AGAINST ('records:"cucumber dragonfruit"');
Empty set (0.008 sec)
SELECT idFROM ft_recordsWHERE MATCH (TABLE ft_records) AGAINST ('records:(cucumber dragonfruit~100)');
+------+
| id |
+------+
| 1 |
+------+
In the above examples, you can see that the phrase query cucumber dragonfruit
does not match because they belong to different leaf strings.fts2_
.
Full-Text Search Legacy Version MATCH with a SQL Predicate
CREATE TABLE books (id INT UNSIGNED,name VARCHAR(100),publish_year INT UNSIGNED,body TEXT,SORT KEY (id),FULLTEXT USING VERSION 1 (body));
The USING VERSION 1
syntax is optional.
SELECT count(*)FROM booksWHERE publish_year = 2017 AND MATCH (body) AGAINST ('memsql');
Full-Text Search Legacy Version MATCH 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 USING VERSION 1 (title, body));
The USING VERSION 1
syntax is optional.
SELECT * FROM articlesWHERE MATCH (title,body)AGAINST ('database');
Full-Text Search Legacy Version MATCH Using a Single Operator
SELECT titleFROM articlesWHERE MATCH (title) AGAINST ('Database OR "Business Intelligence"');
Full-Text Search Legacy Version MATCH Using Multiple Operators
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");
Full-Text Search Legacy Version MATCH for Any id
with a Specific Naming Convention
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*');
Full-Text Search Legacy Version MATCH Using a Wildcard
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*');
Full-Text Search Legacy Version MATCH Using Special Characters
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*');
Full-Text Search Legacy Version MATCH Using Special Characters and Operators
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?');
Full-Text Search Legacy Version MATCH Creating a Relevance Score as an Output Column
SELECT id, title, MATCH(body) AGAINST ('database') relevanceFROM articlesWHERE MATCH(body) AGAINST ('database');
Full-Text Search Legacy Version MATCH with Greater than a Specific Relevance Score
SELECT id, title, MATCH(body) AGAINST ('database')FROM articlesWHERE MATCH(body) AGAINST ('database') > .12;
Full-Text Search Legacy Version MATCH 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: August 14, 2024