Working with Full-Text Search

Version 2 SingleStore Process

SingleStore's VERSION 2 full-text search system uses a JLucene service. The JLucene service is a Java process that provides an interface for the SingleStore engine. This interface allows the engine to perform full-text searches and create full-text search indexes for future use. The JLucene service uses software from the Apache Lucene project. The SingleStore engine and the JLucene service run on the same machine. Communication between the two processes occurs through domain sockets and shared memory. Typically, communication occurs on a per-segment basis.

The default analyzer for VERSION 2 is the StandardAnalyzer from Apache Lucene configured to use the StandardTokenizer from Apache Lucene, a lower-case filter, and a set of stopwords. The StandardTokenizer is a grammar-based tokenizer which uses the word break rules from the Unicode Text Segmentation, as specified in Unicode Standard Annex #29.

Terms in Full-Text Searches

There are two types of search terms: single terms and phrases. A single term is a single word such as test or hello which does not require quotes. A phrase is a group of words surrounded by double quotes such as "hello SingleStore". Multiple search terms can be combined with Boolean operators to form more complex queries.

Search terms can be modified to provide a wide range of search options as described below.

SELECT * FROM wilsearch1 WHERE MATCH (TABLE wilsearch1) AGAINST ('col1:te?t');

SELECT * FROM wilsearch1 WHERE MATCH (TABLE wilsearch1) AGAINST ('col1:te*t');

SELECT * FROM bstsearch1 WHERE MATCH (TABLE bstsearch1) AGAINST ('col1:"Single Store"^4 "MySQL"');

SELECT * FROM bssearch1 WHERE MATCH (TABLE bstsearch1) AGAINST ('col1:SingleStore MySQL^0.02');

Operators in Full-Text Search

Full-text version 2 supports operators listed on the Java Lucene full-text search string syntax page.

Grouping Terms in a Query

SELECT * FROM grpsearch1 WHERE MATCH (TABLE grpsearch1) AGAINST ('col1:SingleStore OR col2:MemSQL');

Escaping Special Characters

Special characters that are part of the query syntax must be escaped to directly match them. These special characters are:

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

Use the \ character before the special character to escape it. For example, to search for (1+1):2 ,use the query \(1\+1\)\:2.

Regular Expression Searches

Regular expression searches that match a pattern between forward slashes ("/") are supported. For example, to find rows containing "moat" or "boat", use /[mb]oat/:

SELECT * FROM rexsearch1 WHERE MATCH (TABLE rexsearch1) AGAINST ('col1:/[mb]oat/'); 

The following example searches for dates with the format of month/day/year where the month and day can have one or two digits and year can have two or four digits.

SELECT * FROM rexsearch1 WHERE MATCH (TABLE rexsearch1) AGAINST ('col1:/\d{1,2}\/\d{1,2}\/\d{2,4}/');

Fuzzy Searches

Fuzzy searches based on the Damerau-Levenshtein Distance are supported. To perform a fuzzy search use the tilde ("~") symbol at the end of a single term. For example, to search for a term similar in spelling to "roam" use the fuzzy search: roam~, which will find terms like "foam" and "roams".

An edit is a change made to a search term to transform it into another string. An optional “edit” parameter can be used after the tilde to specify the maximum number of edits permitted. This value can be 0, 1, or 2. If not specified, the default value is 2 edits.

The following is an example of a fuzzy search of the term "roam" without the optional parameter:

SELECT * FROM fuzsearch1 WHERE MATCH (TABLE fuzsearch1) AGAINST ('col1:roam~');

The following is an example of a fuzzy search of the term "roam" with the optional parameter:

SELECT * FROM fuzsearch1 WHERE MATCH (TABLE fuzsearch1) AGAINST ('col1:roam~1');

MATCH (TABLE <table_name>) AGAINST (<col_name$key.path:search_term> OPTIONS <json_options>);

SELECT * FROM articles WHERE MATCH (TABLE articles) AGAINST ('body:roam~' OPTIONS '{"fuzzy_prefix_length": 2}');

SELECT * FROM articles 
    WHERE MATCH (TABLE articles) AGAINST ('body:roam~' OPTIONS '{"fuzzy_prefix_length": 1, "fuzzy_transpositions": false}');

Proximity Searches

Finding words within a specified distance away is supported. To perform a proximity search, use the tilde ("~") symbol at the end of a search phrase. For example, to search for a "Single" and "Store" within 10 words of each other in a row, use the search "Single Store"~10:

SELECT * FROM prxsearch1 WHERE MATCH (TABLE prxsearch1) AGAINST ('col1:"Single Store"~10');

Range Searches

Range searches match rows where the column(s) values are between the lower and upper bound specified by the query. Range searches can be inclusive or exclusive of the upper and lower bounds. Sorting is done lexicographically or by value depending on whether the search is over string or numeric values.

Square brackets ("[]") indicate an inclusive search, curly brackets ("{}") indicate an exclusive search. A range clause of [123 TO 345] implies a numeric range search, a range clause of [abc TO def] implies a lexicographic range search, and [$123$ TO $345$] forces a lexicographic range despite the numeric range values. Specific examples are provided below.

An inclusive range search matches all rows where the values are between the search terms, including values equal to the search terms. Inclusive range queries are denoted by square brackets (“[ ]”). For example, the following query searches for rows where the numeric value of col1 is 2008 through 2020:

SELECT * FROM rngsearch1 
WHERE MATCH (TABLE rngsearch1) AGAINST ('col1:[2008 TO 2020]');

An exclusive range search matches all rows where the values are between the search terms, but are not equal to the search terms. Exclusive range queries are denoted by curly brackets (“{ }”). For example, the following query searches for rows where the numeric value of col1 is between 2008 and 2020, but is not equal to 2008 or 2020:

SELECT * FROM rngsearch1 
WHERE MATCH (TABLE rngsearch1) AGAINST ('col1:{2008 TO 2020}');

Range searches may be lexicographic or numeric. Typically searches over strings are expected to order lexicographically while searches over numeric values are expected to order by numeric value. The type of range search to use (lexicographic or numeric) is determined by the values entered in the range clause in the query. If both the upper and lower bounds of the range are numeric, then a numeric range search will be performed. If either value is not numeric, then a lexicographic query will be used.

The following query will use a numeric search as both the values 2008 and 2020 are numeric.

SELECT * FROM rngsearch1 
WHERE MATCH (TABLE rngsearch1) AGAINST ('col1:{2008 TO 2020}');

The following query will use a lexicographic search as both of the range values are strings that do not contain numeric values. Note that this query is inclusive of A, but exclusive of B.

SELECT * FROM rngsearch1 
WHERE MATCH (TABLE rngsearch1) AGAINST ('col1:[A TO B}');

This query will use a numeric search as both the values "2008" and "2020" can be parsed numerically.

SELECT * FROM rngsearch1 
WHERE MATCH (TABLE rngsearch1) AGAINST ('col1:{"2008" TO "2020"}');

It is possible to force a lexicographic search on a numeric-style range clause. To do so, surround the numeric upper and lower bounds with the '$' character. The following query will use a lexicographic search.

SELECT * FROM rngsearch1 
WHERE MATCH (TABLE rngsearch1) AGAINST ('col1:[$2008$ TO $2020$]'

ORDER BY … LIMIT Optimization

Full-text search queries can take advantage of an optimization that pushes down the value of LIMIT in an ORDER BY … LIMIT query into the full-text index search. This optimization will reduce the number of results returned from the full-text index search which can increase query performance.

For this optimization to work, the query must follow these rules:

• The WHERE clause must either be empty or the expression in the WHERE clause must be the same full-text expression that is in the ORDER BY clause.

• The ORDER BY clause must use DESC sort order.

• There must be only one full-text search function (MATCH, BM25, or BM25_GLOBAL) in the query.

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.

Status

The Alloc_fts2_svc status variables is the number of allocated bytes (out of the total max_memory) that is attributable to the next-generation (VERSION 2) full-text subprocess. This status variable will be exposed in the SHOW STATUS EXTENDED command.

Configurations

Below is a set of global variables that can be used to configure full-text search version 2.

If your system is experiencing high load due to full-text index builds, SingleStore recommends reducing the value of fts2_max_connections to 16 or 8. This change will reduce load, but will slow down indexing, but will reduce load.

Examples

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.'),
   (11, 2020, 'Real-time Data Analysis', 'An introduction to real-time analytics.'),
   (12, 2021, 'Analysis for Beginners', 'Simple examples of real time analytics.'),
   (13, 2023, 'Data-Dictionary Design', 'Create and maintain effective data dictionaries.'),
   (14, 2024, 'Scalable Performance', 'Designing for scalability.');

OPTIMIZE TABLE articles FLUSH;

SELECT * 
FROM articles 
WHERE 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 title 
FROM articles 
WHERE MATCH (TABLE articles) AGAINST ('title:Database OR body:("Business Intelligence")');

+---------------------------------+
| title                           |     
+---------------------------------+
| Introduction to Database Design |
| Database Optimization           |
+---------------------------------+

SELECT title 
FROM articles 
WHERE MATCH (TABLE articles) AGAINST ('title:SQL AND title:("Data Security")');

+----------------------+
| title                |     
+----------------------+
| Data Security in SQL |
+----------------------+

SELECT title
    FROM articles  
    WHERE MATCH (TABLE articles) AGAINST ('title:SQL OR body:("Business Intelligence")')
    AND MATCH (TABLE articles) AGAINST ('body:web+');

+------------------------+
| title                  |     
+------------------------+
| SQL in Web Development |
+------------------------+

SELECT title
    FROM articles  
    WHERE MATCH (TABLE articles) AGAINST ('title:(+Data*) OR title:function?');

+---------------------------------+
| title                           |     
+---------------------------------+
| Introduction to Database Design |
| SQL and Data Analysis           |
| Database Optimization           |
| Data Security in SQL            |
| Data-Dictionary Design          |
| Real-time Data Analysis         |
+---------------------------------+

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;

SELECT title, records 
FROM ft_records WHERE id = 1;

+----------+--------------------------------------------------------------------------------------------------------------------------------------------+ 
| title	   | records                                                                                                                                    |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------+
| document | {"k1":"cucumber","k2":["dragonfruit","eggplant"],"k3":[{"k3_1":"fig","k3_2":"grape"},{"k3_1":["huckleberry","iceberg lettuce"]},"jicama"]} | 
+----------+--------------------------------------------------------------------------------------------------------------------------------------------+

{
	"title": "document",
	"records": ["cucumber", "dragonfruit", "eggplant",
                   "fig", "grape", "huckleberry", 
          	      "iceberg lettuce", "jicama"],
    	"records$k1": "cucumber",
    	"records$k2": ["dragonfruit", "eggplant"],
   	"records$k3": "jicama",
    	"records$k3.k3_1": ["fig", "huckleberry", "iceberg lettuce"],
    	"records$k3.k3_2": "grape"
}

SELECT (MATCH (TABLE ft_records) AGAINST ('records:/.*cumber/')) AS cumber
FROM ft_records;

+----------+ 
| cumber   |
+----------+ 
| 1        | 
+----------+

SELECT (MATCH (TABLE ft_records) AGAINST ('records$k3.k3_1:fig')) AS fig
FROM ft_records;

+------------------------+ 
| fig                    |
+------------------------+ 
| 0.13076457381248474    | 
+------------------------+

SELECT id 
FROM ft_records 
WHERE MATCH (TABLE ft_records) AGAINST ('records:(iceberg lettuce)'); 

+------+ 
| id   |
+------+ 
| 1    | 
+------+ 

SELECT id 
FROM ft_records 
WHERE MATCH (TABLE ft_records) AGAINST ('records:"cucumber dragonfruit"');

Empty set (0.008 sec)

SELECT id 
FROM ft_records 
WHERE MATCH (TABLE ft_records) AGAINST ('records:(cucumber dragonfruit~100)');

+------+
| id   | 
+------+ 
| 1    | 
+------+ 

SELECT id, title, 
   MATCH (TABLE articles) AGAINST ('body:database') AS match_res
FROM articles 
WHERE match_res
ORDER BY match_res DESC LIMIT 25;

Legacy (Version 1) SingleStore Process

SingleStore's legacy full-text search system uses a CLucene service which is embedded in the SingleStore database engine.

The following example illustrates how to create a table with a legacy version full-text search index and how to query from that table. The USING VERSION 1 syntax is optional.

CREATE TABLE articles (
    id INT UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    SORT KEY (id),
    FULLTEXT USING VERSION 1 (title, body));

SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database');

Refer to the MATCH page for more examples.

Index Repair

Full-text index creation failure is rare. However, if full-text index creation fails, you will receive the error ER_FTS_INDEX_NEEDS_REPAIR_ON_SEGMENT. The index can be repaired by running OPTIMIZE TABLE <tablename> FIX_FULLTEXT.

Related Topics

• Working with Vector Data- Allows for semantic searching which is searching based on meanings, not keywords.

• Hybrid Search - Allows full-text and vector search methods in one query. Full-text and vector search ranking can be combined.

• MATCH

• HIGHLIGHT - HIGHLIGHT is not supported in VERSION 2.

• Training: Full-Text Index and Search