#Indexes

Indexes help SQLite narrow the candidates for a selective query. They are most useful when a query should return a small part of a large collection, or when a $lookup repeatedly probes a foreign key.

Silt stores index definitions and keys in the same database as the documents. SQL triggers maintain keys when documents change. Queries still apply the complete SQL predicate, preserving the filter's semantics after the index has narrowed the candidates.

#Create a compound index

The following self-contained example indexes orders by tenant, status, and amount.

import { openDatabase } from '@silt-db/sqlite';

const db = await openDatabase(':memory:');
const orders = db.collection('orders');

await orders.insertMany([
  {
    _id: 'o1',
    tenantId: 'shop-1',
    status: 'open',
    amount: 25,
    customerId: 'alice',
    tags: ['priority', 'gift'],
  },
  {
    _id: 'o2',
    tenantId: 'shop-1',
    status: 'open',
    amount: 10,
    customerId: 'bob',
    tags: ['gift'],
  },
  {
    _id: 'o3',
    tenantId: 'shop-2',
    status: 'closed',
    amount: 50,
    customerId: 'carol',
    tags: [],
  },
]);

const indexName = await orders.createIndex(
  { tenantId: 1, status: 1, amount: -1 },
  { name: 'orders_by_tenant_status_amount' },
);

const query = orders.find({
  tenantId: 'shop-1',
  status: 'open',
  amount: { $gte: 20 },
});

console.log(indexName);
console.log(await query.toArray()); // The order with _id 'o1'.

An eligible query can use equality conditions on leading fields followed by a range on the next field. A query only on amount does not have the same access pattern because the preceding fields are unconstrained.

Call createIndex() during application setup or a deliberate migration. Creating a new index backfills existing documents and adds work to future writes. Repeating the same name and specification is idempotent; using an existing name for a different specification fails. On a file database, definitions survive closing and reopening.

#Verify the actual plan

Use explain() on the query you intend to run:

const plan = await query.explain();
console.log(plan.queryPlan);

The plan includes SQLite's SEARCH or SCAN steps and their details. Look for an indexed search of the auxiliary key table for eligible filters. Additional scans can belong to JSON traversal or exact comparison rather than the whole source collection.

An index does not guarantee every stage avoids a scan or sort. Silt currently selects conservative index candidates; unsupported access patterns continue through a correct SQL scan. Do not assume MongoDB planner features such as index hints, covered projections, or sort elimination are available. The database reference describes SQL inspection methods.

#Index an array field

A multikey index creates candidates for array elements, so a tag filter can use an index:

await orders.createIndex({ tags: 1 }, { name: 'orders_by_tag' });

const priorityOrders = await orders.find({ tags: 'priority' }).toArray();
console.log(priorityOrders.length); // 1

A document appears once in the final query result even if multiple elements match. Array expansion can produce more index entries than documents, increasing storage and maintenance work.

Compound indexes support tested multikey patterns, but independent parallel arrays are rejected. For example, a compound index over tags and categories cannot accept a document in which both are independent arrays. A failed index build or write rolls back atomically. Shared array paths and $elemMatch require their own semantic handling; see the compatibility contract before relying on an advanced pattern.

#Index the foreign side of a lookup

If a customer pipeline joins to orders using foreignField: 'customerId', create the index on orders:

await orders.createIndex({ customerId: 1 });

The aggregation guide includes a complete seeded example of this lookup. Eligible equality and correlated equality joins can use the foreign index; a general per-customer comparison may still require a scan. Inspect the entire aggregation's explain() output to confirm the executed plan.

String _id lookups have a built-in unique key path. You normally do not need a separate secondary index for a lookup against a collection's string _id.

#Choose indexes for your workload

Start with frequent selective filters and foreign lookup keys. Measure reads and writes with representative data before adding more indexes: a faster read can come with a substantial write and storage cost, especially for large arrays.

The current secondary indexes are nonunique. Unique secondary, sparse, partial, TTL, text, and geospatial indexes are not implemented. Individual index removal is also not currently exposed by the collection API. Dropping a collection removes its documents and indexes together; use that operation only when you intend to remove the data.

Close the example database after trying the snippets:

await db.close();

Next, transactions show how to commit related writes together.