Working with Full-Text Search
On this page
Full-text search allows searching for words or phrases in a large body of text through an inverted index.fuzzy
and performed over the text types CHAR
, VARCHAR
, TEXT
, and LONGTEXT
.
Full-text indexes are only supported on columnstore tables.CREATE TABLE
query using the FULLTEXT index type.
CREATE TABLE <table_name> (FULLTEXT [<fts_index_name>] (<fts_col>))
Note
If an index name was not designated when creating the table, the full-text index key_
must be used when dropping the index.key_
is displayed when the SHOW INDEXES FROM <table_
command is executed.
Content in columns that are full-text indexed can be searched using the MATCH syntax.MATCH
clause applies to only one table.MATCH
clauses.
Note
New inserts and updates into columnstore tables may initially be stored in a hidden rowstore table 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.OPTIMIZE TABLE <table_
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.
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
Related Topics
-
Vector Search - Allows for semantic searching which is searching based on meanings, not keywords.
-
Hybrid Search - Allows full-text and search methods in one query.
Full-text and vector search ranking can be combined. -
Training: Full-Text Index and Search
Last modified: July 24, 2024