Building a full-text search engine from scratch involves segmenting text into tokens, storing those tokens in optimized structures, and resolving queries through inverted index lookups. The core workflow is straightforward: initialize the search system, generate indexes on target columns, parse user queries into individual words, match those words against the index to find relevant rows, and finally construct a result set.

Indexing Infrastructure

A custom full-text solution relies on a set of internal tables inside a dedicated schema (e.g., FT):

Index Registry – Records metadata for each created index:

CREATE TABLE FT.INDEX_REGISTRY(
    IDX_ID INT AUTO_INCREMENT PRIMARY KEY,
    DB_SCHEMA VARCHAR(255),
    DB_TABLE VARCHAR(255),
    COLUMN_LIST VARCHAR(1024),
    UNIQUE(DB_SCHEMA, DB_TABLE)
);

Lexicon – Stores every unique token extracted from indexed text:

CREATE TABLE FT.LEXICON(
    TOKEN_ID INT AUTO_INCREMENT PRIMARY KEY,
    TOKEN_VALUE VARCHAR(255),
    UNIQUE(TOKEN_VALUE)
);

Row Store – Maps a hash of the primary key condition to a specific index:

CREATE TABLE FT.ROW_STORE(
    ROW_HANDLE INT AUTO_INCREMENT PRIMARY KEY,
    RECORD_HASH INT,
    INDEX_ID INT,
    PK_CONDITION VARCHAR(1024),
    UNIQUE(RECORD_HASH, INDEX_ID, PK_CONDITION)
);

Inverted Mapping – Connects tokens to the rows that contain them:

CREATE TABLE FT.INVERTED_MAP(
    ROW_HANDLE INT,
    TOKEN_ID INT,
    PRIMARY KEY(TOKEN_ID, ROW_HANDLE)
);

Stop-words List – Common words that shouldn’t be indexed:

CREATE TABLE FT.STOP_WORDS(
    WORD_ENTRY VARCHAR(255)
);

Keeping the Index Synchronzied

Triggers on the source table invoke an update procedure whenever rows are inserted, modified, or removed. This guarantees that the FT schema remains accurate without manual intervention.

Search Query Flow

When a user calls a search function like FT_SEARCH('waterproof backpack', 20, 0):

1. The input string is tokenized into waterproof and backpack.
2. Each token is looked up in the LEXICON table to retrieve its TOKEN_ID.
3. The INVERTED_MAP table is scanned too gather all distinct ROW_HANDLE values.
4. Those handles are resolved against ROW_STORE to obtain primary key conditions such as ProductID = 45.
5. A final query against the original table returns the matched records.

Practical Walkthrough: Product Catalog

Assume a ProductCatalog table with columns id, title, and summary. After creating an index:

FT_CREATE_INDEX('PUBLIC', 'ProductCatalog', 'title, summary');

Index Registry sample row:

IDX_ID | DB_SCHEMA | DB_TABLE        | COLUMN_LIST
-------|-----------|-----------------|--------------
1      | PUBLIC    | ProductCatalog  | title, summary

Lexicon sample values:

TOKEN_ID | TOKEN_VALUE
---------|------------
101      | waterproof
102      | backpack

Inverted Map associations:

ROW_HANDLE | TOKEN_ID
-----------|---------
201        | 101
202        | 102
201        | 102

For the query 'waterproof backpack', the engine identifies row handles 201 and 202, converts them into WHERE id = 201 OR id = 202, and returns matching products. Large offsets and hit limits are supported through parameterization. This design keeps full-text capabilities entirely within the database, avoiding external dependencies while serving complex search requirements.