WL#10744: Full-Text Index Type

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Support creation of Full-Text Index of a Sub-Document

Full-Text indexes allow searching the entire document (or a sub-document) for any text value contained.

Full-Text search of JSON documents may require a custom fulltext parser and/or additional querying extensions to be fully useful (e.g. json_textcontains() from Oracle).

External Requirements

A xprotocol client

X1
MUST be able to create a FULLTEXT index on document of a collection
X2
MUST be able to create a FULLTEXT index on a sub-document of a collection
X3
MUST be able to optionally specify the name of a fulltext parser for a FULLTEXT index
X4
MUST be able to specify the name of the index
X5
MUST be able to specify if the (sub-)document has to exist
X6
MUST be able to drop the index by name via the drop_index admin command.
X7
MUST receive an Error message if the create_fulltext_index operation fails.

Internal Requirements

The create_fulltext_index operation

F1
MUST fail if generated ALTER TABLE statement fails like
  • wrong fulltext parser
  • names too long
  • index with the same name exists
  • generated column with the same name exists
F2
MUST fail if a 'unique' FULLTEXT is requested

Background

In the xplugin the create_collection_index StmtExecute command of the mysqlx namespace is used to create indexes for collections.

See: admin_cmd_handler.cc

It takes the following arguments:

  • schema: string
  • collection: string
  • name: string
  • unique: bool
  • type: string [optional, default='INDEX', allowed=INDEX|SPATIAL]
  • constraint: object or list-of-objects
    • member: docpath
    • required: bool
    • type: string

Adding FULLTEXT support

new type: FULLTEXT

A new value for .type is to be supported in the index field specification:

FULLTEXT

new, optional .with_parser

To specify the fulltext parser plugin a FULLTEXT index may specify:

with_parser: string

default constraint.type

If no constraint.type is given, it defaults to:

TEXT(64)

parameter schema

  • schema: string
  • collection: string
  • name: string
  • unique: bool
  • type: string [optional, default='INDEX', allowed=INDEX|SPATIAL|FULLTEXT]
  • with_parser: string [optional, default=empty]
  • constraint: object or list-of-objects
    • member: docpath
    • required: bool
    • type: string [optional]

SQL generation

ALTER TABLE, ADD COLUMN part:

ADD COLUMN <col> TEXT
  GENERATED ALWAYS AS (JSON_EXTRACT(doc, <member>)) STORED

Notes:

  • FULLTEXT indexes can currently only be created on STORED generated columns, not VIRTUAL
  • FULLTEXT indexes cannot be created on a JSON column
  • FULLTEXT indexes allow NULLable columns

ALTER TABLE, ADD INDEX part:

ADD FULLTEXT INDEX <name> (<col>) WITH PARSER <with_parser>

Notes:

  • the content of with_parser expects an identifier. Use identifier quoting if special characters are used.

Error and Warnings

Property Value
Error code ER_X_CMD_ARGUMENT_VALUE
Error text Unique fulltext index is not supported
New error code No
Property Value
Error code ER_X_CMD_ARGUMENT_VALUE
Error text 'with_parser' argument is supported for fulltext index only
New error code No

Example

  • schema: foo
  • collection: col1
  • name: ftidx
  • unique: FALSE
  • type: FULLTEXT
  • with_parser: ngram
  • constraint:
    • member: '$.subdoc'
    • required: FALSE

results in:

ALTER TABLE col1
  ADD COLUMN ft TEXT
    GENERATED ALWAYS AS (doc->>'$.subdoc')) STORED,
  ADD FULLTEXT INDEX ftidx (ft) WITH PARSER ngram;

If with_parser is not specified:

ALTER TABLE col1
  ADD COLUMN ft TEXT
    GENERATED ALWAYS AS (doc->>'$.subdoc')) STORED,
  ADD FULLTEXT INDEX ftidx (ft);