Create full-text indexes and run keyword search from SQL

Keyword search usually means another index and another service. Built-in full-text indexing keeps ranked keyword queries in the workspace (the same engine as the rest of your SQL).

How it works

Step 1: Pick a table and text column

For our support knowledge base, open the help articles table and list its columns. I need to know which one holds the article text.

Step 2: Create a full-text index

Turn on keyword search for the body field of those help articles so we can match phrases quickly.

With --async, BM25 index creation runs as a background job. Poll until it's finished before you count on that index for search.

Step 3: Keyword search from the CLI

Find help articles that talk about billing refunds (about 10 results), and include id, title, and full body text.

Step 4: Same search in SQL (for apps and agents)

Run the same kind of search in SQL: strongest matches first, with id, title, body, and a relevance score. Return 10 rows.

Who uses this

• Support knowledge bases with huge article corpora.
• Internal apps that used to rely on LIKE and crossed fingers.
• Agents that want ranked rows and relevance metadata on the same path as other SQL.