pingcap / pingcap/tidb

planner, ddl: give MATCH ... AGAINST an index on the classic kernel

Open
#70,491 1 comment 0 reactions 0 assignees View on GitHub
component/expression sig/planner type/enhancement
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.