Indexes

Are your queries taking too long to run? Measure their performance using the logQueryExecutionTime option:

Using the Schema from Define schema:

import { createQueryBuilder } from "@evolu/common";

const createQuery = createQueryBuilder(Schema);
const allTodos = createQuery(
  (db) => db.selectFrom("todo").orderBy("createdAt").selectAll(),
  {
    logQueryExecutionTime: true,
    // logExplainQueryPlan: false,
  },
);

Without an index on the columns a query filters, joins, or sorts by, SQLite reads the whole table, and a nested query, such as one built with evoluJsonArrayFrom, reads it again for each parent row. Measured through Evolu with native SQLite on a MacBook Air, a page of 50 todos sorted by createdAt from a table of 100,000 took about 5 milliseconds with an index and 15 without one, while nesting those todos into 100 projects took about 90 milliseconds with an index on projectId and 700 without one. Browsers and slower devices take longer.

Evolu indexes the id column of every table itself, so a query that finds or joins rows by ID needs no index of its own.

For deeper insights into how SQLite indexes work under the hood, read this in-depth guide.

Usage

Using the schema, shared run, and appOwner from Create Evolu:

import { AppName, createEvolu } from "@evolu/common";

const evolu = await run.ok(
  createEvolu(Schema, {
    appName: AppName.orThrow("MyApp"),
    indexes: (create) => [
      create("todoCreatedAt").on("todo").column("createdAt"),
    ],
    appOwner,
  }),
);

Evolu handles this automatically—it will create any new indexes you define and drop those no longer present in the indexes array.

Unique indexes are rejected because concurrent offline writes can violate uniqueness during synchronization. See Uniqueness for how to handle duplicate business values. Index names starting with evolu_ are reserved for Evolu.

Recommendations

SQLite offers a powerful CLI tool for index recommendations.

To use it:

1. Download the "Precompiled Binaries" here.
2. Open your database or create a new one.
3. Run .expert and paste in the SQL of the query you're analyzing.

You can get the query SQL using the logQueryExecutionTime option in createQuery, which logs the full SQL statement for easy copy-paste.