# Create full-text indexes and run keyword search from SQL

Source: https://www.hotdata.dev/use-cases/text-search
Site index: https://www.hotdata.dev/llms.txt

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

**Claude**

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

**CLI**

```bash
hotdata databases tables show support.public.help_articles
```

**Python SDK**

```python
import hotdata

api = hotdata.InformationSchemaApi(api_client)
api.information_schema(
    connection_id="support",
    var_schema="public",
    table="help_articles",
    include_columns=True,
)
```

### Step 2: Create a full-text index

**Claude**

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

**Python SDK**

```python
import hotdata

indexes = hotdata.IndexesApi(api_client)
indexes.create_index(
    "support",
    "public",
    "help_articles",
    hotdata.CreateIndexRequest(
        index_name="help_body_bm25",
        index_type="bm25",
        columns=["body"],
    ),
)
```

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

**Claude**

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

**CLI**

```bash
hotdata search "billing refund" \
  --index help_body_bm25 \
  --select id,title,body \
  --limit 10
```

**Python SDK**

```python
import hotdata

query_api = hotdata.QueryApi(api_client)
query_api.query(
    hotdata.QueryRequest(
        sql=(
            "SELECT id, title, body FROM bm25_search("
            "'support.public.help_articles', 'body', 'billing refund', 50) "
            "LIMIT 10"
        ),
    ),
)
```

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

**Claude**

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

**CLI**

```bash
hotdata query "SELECT id, title, body, score FROM bm25_search('support.public.help_articles', 'body', 'billing refund', 50) ORDER BY score DESC LIMIT 10"
```

**Python SDK**

```python
import hotdata

query_api = hotdata.QueryApi(api_client)
query_api.query(
    hotdata.QueryRequest(
        sql=(
            "SELECT id, title, body, score FROM bm25_search("
            "'support.public.help_articles', 'body', 'billing refund', 50) "
            "ORDER BY score DESC LIMIT 10"
        ),
    ),
)
```

## 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.