planner, ddl: give MATCH ... AGAINST an index on the classic kernel
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
Give `MATCH ... AGAINST` an index to use on the classic kernel, so it stops being a full scan.
Follows #70486, which made `MATCH ... AGAINST` evaluate correctly without an index but explicitly left the access path out of scope. Related: ref #61185 (indexed execution on the columnar engine).
### Problem
After #70486, a `MATCH ... AGAINST` filter is correct on the classic kernel but is evaluated as a residual over every row: TiDB analyzes each document at read time and matches it against the compiled query. That is fine for correctness and hopeless for a large corpus.
`CREATE FULLTEXT INDEX` is also still rejected outside starter deployments, so there is nothing for the planner to use even in principle.
### Proposed change
Two steps.
**1. Materialise `FULLTEXT INDEX` as a multi-valued index.** On the classic kernel, rewrite
```sql
FULLTEXT INDEX idx (body)
```
into an expression index over the tokenized column:
```sql
INDEX idx ((CAST(FTS_TOKENIZE(`body`, 'STANDARD', 3, 84, 1) AS CHAR(84) ARRAY)))
```
`IndexInfo.FullTextInfo` stays nil, because setting it makes `IsColumnarIndex` report true and suppresses the KV index entirely. The tokenize expression sits on the hidden generated column that expression indexes already create, so it never appears in `SELECT *` or `SHOW COLUMNS`, and `SHOW CREATE TABLE` reports the index as the `FULLTEXT KEY` it was declared as.
The analyzer configuration is resolved from session variables once at DDL time and written into the expression as literals, making the schema the index's analyzer snapshot: the generated column is re-evaluated on every write, so reading session variables at write time instead would let a later `SET` reshape the token stream and disagree with rows already indexed.
**2. Use it as an access path.** Recognise a `MATCH` whose analyzer configuration matches such an index, synthesize `member of` predicates for the query's required terms so the existing multi-valued index-merge machinery can generate candidates, and keep the `MATCH` itself as the residual filter — phrase and prefix semantics cannot be expressed as index predicates and must still be checked per row.
### Out of scope
- Relevance scoring and top-k. The intersection worker materialises the full candidate handle map before emitting any table task, so there is no early termination to exploit even with a score available.
- Cross-server reproducibility of the analyzer snapshot. Like MySQL, the `innodb_ft_*` settings are not carried in `SHOW CREATE TABLE`, so restoring a dump onto a differently configured server tokenizes differently. A `/*T![...]*/` extension comment could carry the snapshot and round-trip exactly; that needs parser grammar work.
- `MULTILINGUAL` parser, which has no local analyzer implementation.
- Stop-word filtering, which is inert today (see #70486).
Contributor guide
Research direction
Start with the classic-kernel DDL path for FULLTEXT INDEX and the planner's multi-valued index-merge handling. Verify that the analyzer snapshot is embedded in the expression, the generated column remains hidden, matching indexes become candidate access paths, and MATCH remains a residual filter; cover both DDL and query execution tests.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100