Working with Full-Text Search

Full-text search allows you to search for words or phrases in a large body of text efficiently. The search can be exact or fuzzy and performed over the text types CHAR, VARCHAR, TEXT, and LONGTEXT.

Overview

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>))

Content in columns that are full-text indexed can be searched using the MATCH ... AGAINST function. Each MATCH clause applies to only one table. To search against multiple tables, specify multiple MATCH clauses.

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.

Full-text search may not be used inside a CTE (WITH (Common Table Expressions)) because the CTE produces a dynamic table which does not have a full-text index. Similarly, full-text search cannot be used on derived tables.

During indexing, column values are split into tokens, which are turned into indexed terms.  The maximum length of an indexed term is 255 bytes.

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.

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.

Last modified:

Was this article helpful?