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
Currently, full text indexes are only supported on columnstore tables. Also, they can only be enabled as part of a
CREATE TABLE query using the FULLTEXT index type. This means full text indexes can’t be dropped or altered after the table is created. If the table is dropped, then then index is deleted automatically.
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
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 fulltext queries, especially when the segments have very few rows and the columns have very few words.
For more information on Columnstore tables, see Columnstore.
For columnstore tables with a full text index, the index is stored as a file on disk and is created when a segment file (on-disk unit of storage for columnstore columns) is created. Its size is roughly equal to the combined compressed sizes of the columns that it is indexing over. Because the index file is created when the segment file is created, this means there will be one full text index per segment file. The full text index’s lifecycle is also matched to the segment file’s lifecycle. Segment files are sometimes merged, and in that case, the old segment data is copied into a new file and the original segment file is deleted. The corresponding full text index file will be deleted when the segment file is deleted.
For more information on segments, see How the SingleStore DB Columnstore Works.
For examples on how to create a table with a
FULLTEXT index and how to search against that table using
MATCH, see MATCH.
- Training: Full Text Index and Search