Sign inTry Free

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. 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. Also, they can only be enabled as part of a CREATE TABLE query using the FULLTEXT index type. This means full-text indexes cannot be dropped or altered after the table is created. If the table is dropped, then the index is deleted automatically.

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_name must be used when dropping the index. The full-text index key_name is displayed when the SHOW INDEXES FROM <table_name> command is executed.

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, specify multiple 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. The affected segment is re-indexed when the background flusher runs.

In that case, the full-text index in the columnstore will be updated asynchronously for new inserts and updates. Inserts and updates from this rowstore table can be force-pushed  to the columnstore table by 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.

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

Last modified: July 24, 2024

Was this article helpful?

On this page

Was this article helpful?