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.

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 MATCH clauses.

Info

Small inserts/updates for columnstore tables go into a hidden rowstore table first and then flushed to a segment file later. This means for small inserts/updates, the full text index will be updated asynchronously; however, you can force push updates from the rowstore table to your columnstore table using OPTIMIZE TABLE tbl_name FLUSH. Larger inserts go straight to the segment file and the full text index will be updated immediately.

For more information on Columnstore tables, see Columnstore.

Index Lifecycle

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 MemSQL Columnstore Works.

Security

To integrate with existing security configurations, full text search works with RBAC, Audit mode, and Strict mode.

Role-based Access Control (RBAC)

Because a full text index is implemented through a column on a table, all the RBAC mechanisms, which are applied at the table level, also apply to full text indexes. There is no other access to the full text index except through query of a table or view.

For more information on RBAC, see the Role-Based Access Control Deployment Guide.

Audit Mode

All interactions with full text search are done through a query, so any DDL and DML operations (and the subsequent data returned) will be audited as per the audit level specified. For more information on audit levels, see Audit Logging Levels.

Strict Mode

There is no change in behavior or functionality in strict mode.

Encryption

Encrypting the drive MemSQL operates on does not affect full text search. The full text index will be encrypted along with all the other MemSQL files located on the encrypted drive.

Examples

For examples on how to create a table with a FULLTEXT index and how to search against that table using MATCH, see MATCH.

Was this article useful?