lancedb/docs/web/indexing/scalar-index.mdx

---
title: "Scalar Indexes"
sidebarTitle: "Scalar Index"
description: "Learn how to use scalar indexes in LanceDB for efficient metadata filtering and query optimization."
icon: "tree"
availability:
  oss: available
  enterprise: available
  summary: >-
    Scalar indexes are built in the embedded library and in LanceDB Enterprise. A
    deployment builds them in the background and keeps most of them up to date
    itself; in the embedded library you update them with `optimize()`.
---

import {
    PyScalarIndexBuild as ScalarIndexBuild,
    PyScalarIndexWait as ScalarIndexWait,
    PyScalarIndexOptimize as ScalarIndexOptimize,
    PyScalarIndexFilter as ScalarIndexFilter,
    PyScalarIndexPrefilter as ScalarIndexPrefilter,
    PyScalarIndexUuidType as ScalarIndexUuidType,
    PyScalarIndexUuidData as ScalarIndexUuidData,
    PyScalarIndexUuidTable as ScalarIndexUuidTable,
    PyScalarIndexUuidWait as ScalarIndexUuidWait,
    PyScalarIndexUuidUpsert as ScalarIndexUuidUpsert,
    PyScalarIndexNestedFields as ScalarIndexNestedFields,
} from '/snippets/indexing.mdx';

Scalar indexes accelerate filters on values such as numbers, categories and
strings. Use them for selective table queries or metadata filters combined
with vector and full-text search.

LanceDB supports four types of scalar indexes:

- `BTREE`: Stores column data in sorted order for binary search. Best for columns with many unique values.
- `BITMAP`: Uses bitmaps to track value presence. Ideal for columns with few unique values (e.g., categories, tags).
- `LABEL_LIST`: Special index for `List<T>` and `LargeList<T>` columns of primitive values supporting `array_contains_all` and `array_contains_any` queries.
- `FM`: FM-Index over string or binary columns that accelerates substring search via `contains(col, 'needle')`.

<Tip title="Working with scalar indices">
Use scalar indexes with:

- Vector search (prefilter results using metadata)
- Full-text search (combining keyword matching with structured filters) 
- SQL scans (optimizing WHERE clauses on scalar columns)
- Key-value lookups (enabling rapid primary key-based retrievals)
</Tip>

## Choosing the Right Index Type

| Data Type                                                       | Filter                                    | Index Type   |
|:----------------------------------------------------------------|:------------------------------------------|:-------------|
| Numeric, String, Temporal                                       | `<`, `=`, `>`, `in`, `between`, `is null` | `BTREE`      |
| Boolean, numbers or strings with fewer than 1,000 unique values | `<`, `=`, `>`, `in`, `between`, `is null` | `BITMAP`     |
| List of low cardinality of numbers or strings                   | `array_has_any`, `array_has_all`          | `LABEL_LIST` |
| String or binary (`Utf8`, `LargeUtf8`, `Binary`, `LargeBinary`) | `contains(col, 'needle')`                 | `FM`         |

## Scalar Index Operations

### 1. Build the Index

You can create multiple scalar indexes within a table. By default, the index will be `BTREE`, but you can always configure another type like `BITMAP`

<CodeGroup>
    <CodeBlock filename="Python" language="Python" icon="python">
    {ScalarIndexBuild}
    </CodeBlock>
</CodeGroup>

<Note title="LanceDB Enterprise">
If you are using LanceDB Enterprise, the `create_scalar_index` API returns immediately, but the building of the scalar index is asynchronous. To wait until all data is fully indexed, you can specify the `wait_timeout` parameter on `create_scalar_index()` or call `wait_for_index()` on the table.
</Note>

### 2. Check Index Status

<CodeGroup>
    <CodeBlock filename="Python" language="Python" icon="python">
    {ScalarIndexWait}
    </CodeBlock>
</CodeGroup>

`wait_for_index(...)` waits until the named scalar indexes exist and `index_stats(...)` reports `num_unindexed_rows == 0`. If a table is receiving steady writes, that fully indexed state may not stabilize before the timeout. In LanceDB Enterprise, rows added after the index was built are indexed only once enough have accumulated, so a small remainder can also keep the wait from finishing.

### 3. Update the Index

Updating the table data (adding, deleting, or modifying records) requires that you also update the scalar index. In the embedded library, call `optimize`, which will trigger an update to the existing scalar index.

<CodeGroup>
    <CodeBlock filename="Python" language="Python" icon="python">
    {ScalarIndexOptimize}
    </CodeBlock>
</CodeGroup>

<Note title="LanceDB Enterprise">
New data added after creating the scalar index will still appear in search results if optimize is not used, but with increased latency due to a flat search on the unindexed portion. LanceDB Enterprise does not support `optimize()`; instead it adds new rows to `BTREE`, `BITMAP` and `LABEL_LIST` indexes in the background once enough have accumulated. It does not update `FM` indexes automatically.
</Note>

### 4. Run Indexed Searches

The following scan will be faster if the column `book_id` has a scalar index:

<CodeGroup>
    <CodeBlock filename="Python" language="Python" icon="python">
    {ScalarIndexFilter}
    </CodeBlock>
</CodeGroup>

Scalar indexes can also speed up scans containing a vector search or full text search, and a prefilter:

<CodeGroup>
    <CodeBlock filename="Python" language="Python" icon="python">
    {ScalarIndexPrefilter}
    </CodeBlock>
</CodeGroup>

## Indexing nested fields

Scalar indexes can target a scalar field inside a struct by passing its full dotted path. The path is preserved end to end: it's the value you pass to `create_scalar_index`, it's what `list_indices()` reports under `columns`, and it's the column reference you use in filter predicates.

```python
# Schema: pa.struct([pa.field("user_id", pa.int32())]) stored under the `metadata` column.
table.create_scalar_index("metadata.user_id", name="metadata_user_id_idx")

# The same dotted path works in WHERE clauses.
table.search().where("metadata.user_id = 42").limit(1).to_list()
```

<Note>
Nested paths follow Lance field-path semantics: dot-separate each struct field from root to leaf (for example, `metadata.author.name`). The same convention applies to FTS and vector indexes.
</Note>

## FM-Index for substring search

The `FM` index is a scalar index built over string or binary columns that
accelerates substring lookups expressed as `contains(col, 'needle')`. Unlike the
tokenized [FTS index](/indexing/fts-index), which matches whole words after
tokenization, the FM-Index matches arbitrary substrings of the raw bytes. It
works well for URLs, file paths, identifiers, log lines, or any column where you
search for a fragment rather than a word.

Use the FM-Index when:

- Filters use `contains(col, 'needle')` (substring), not equality or word search.
- The column is `Utf8`, `LargeUtf8`, `Binary`, or `LargeBinary`.
- You want substring matches without paying for tokenization, language analysis,
  or BM25 scoring.

Pick `FTS` instead when you need word-level relevance ranking, phrase queries,
or language-aware tokenization.

### Create an FM-Index

Build an FM-Index with the async `create_index` API by passing the `Fm` config in
Python or `Index.fm()` in TypeScript. In Rust, use `Index::Fm(FmIndexBuilder::default())`.
A deployment builds FM-Indexes too, but does not add new rows to them in the background as it
does for the other scalar indexes.

<CodeGroup>
```python Python icon="python"
from lancedb.index import Fm

await tbl.create_index("text", config=Fm())
```

```typescript TypeScript icon="square-js"
import * as lancedb from "@lancedb/lancedb";
import { Index } from "@lancedb/lancedb";

await tbl.createIndex("text", { config: Index.fm() });
```

```rust Rust icon="rust"
use lancedb::index::Index;
use lancedb::index::scalar::FmIndexBuilder;

tbl.create_index(&["text"], Index::Fm(FmIndexBuilder::default()))
    .execute()
    .await?;
```
</CodeGroup>

After the index is built, substring filters use it automatically:

```python
table.search().where("contains(text, 'needle')").limit(10).to_pandas()
```

`list_indices()` reports the index type as `"Fm"`.

## Index UUID Columns

LanceDB supports scalar indexes on UUID columns (stored as `FixedSizeBinary(16)`), enabling efficient lookups and filtering on UUID-based primary keys.

<Note>
**To use `FixedSizeBinary`, ensure you have:**

- Python SDK version `0.22.0` or later
- TypeScript SDK version `0.19.0` or later
</Note>

### 1. Define UUID Type

<CodeGroup>
    <CodeBlock filename="Python" language="Python" icon="python">
    {ScalarIndexUuidType}
    </CodeBlock>
</CodeGroup>

### 2. Generate UUID Data

<CodeGroup>
    <CodeBlock filename="Python" language="Python" icon="python">
    {ScalarIndexUuidData}
    </CodeBlock>
</CodeGroup>

### 3. Create Table with UUID Column

<CodeGroup>
    <CodeBlock filename="Python" language="Python" icon="python">
    {ScalarIndexUuidTable}
    </CodeBlock>
</CodeGroup>

### 4. Create and Wait for the Index

<CodeGroup>
    <CodeBlock filename="Python" language="Python" icon="python">
    {ScalarIndexUuidWait}
    </CodeBlock>
</CodeGroup>

### 5. Perform Operations with the UUID Index

<CodeGroup>
    <CodeBlock filename="Python" language="Python" icon="python">
    {ScalarIndexUuidUpsert}
    </CodeBlock>
</CodeGroup>

## Index nested fields

You can build a scalar index on a field inside a struct column by passing the
canonical dot-separated path to `create_index`. This is useful when filters
target attributes nested under a `metadata`-style column, for example
`metadata.user_id` or `metadata.event.type`.

If a literal segment of the path itself contains a dot (for example a column
named `user.id` nested inside `metadata`), wrap that segment in backticks so
LanceDB can tell the dot apart from the path separator: `` metadata.`user.id` ``.

`list_indices()` echoes the same canonical path back, so the column you pass in
round-trips through index metadata regardless of nesting depth or escaping.

<CodeGroup>
    <CodeBlock filename="Python" language="Python" icon="python">
    {ScalarIndexNestedFields}
    </CodeBlock>
</CodeGroup>

<Note>
Composite indexes that cover multiple columns aren't supported yet. Each
`create_index` call must target a single (possibly nested) field path.
</Note>