Working with Full-Text Search
Full-text search allows searching for words or phrases in a large body of text through an inverted index. The search can be exact or “fuzzy” and performed over the text types CHAR, VARCHAR, TEXT, and LONGTEXT.
Full-text indexes are only supported on columnstore tables. They can be enabled as part of a CREATE TABLE statement using the FULLTEXT index type. They can also be added to existing tables via an ALTER TABLE ADD FULLTEXT statement. SingleStoreDBsupports only one full-text index per table. Full-text indexes can be dropped via DROP INDEX or ALTER TABLE DROP INDEX statements.
Content in columns that are full-text indexed can be searched using the MATCH syntax. Each MATCH clause applies to only one table. To search against multiple tables, you need to specify multiple MATCH clauses.
Note
New inserts and updates into columnstore tables may go into a hidden rowstore table first before being flushed to a segment file. In that case, the full-text index in the columnstore will be updated asynchronously for new inserts and updates; however, you can force push inserts and updates from the rowstore table to your columnstore table using the OPTIMIZE TABLE <table_name> FLUSH command.
Since an index is created for each segment file, the distribution of words within the segment may affect the score of full-text queries, especially when the segments have very few rows and the columns have very few words.
For more information on Columnstore tables, see How the Columnstore Works.
Examples
For examples on how to create a table with a FULLTEXT index and how to search against that table using MATCH, see MATCH.