# Indexes

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

Using the `Schema` from [Define schema](https://evolu.dev/docs/local-first#define-schema):

```ts

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

> While indexes may not be needed during early development, they are crucial for
> production performance. Use the `logQueryExecutionTime` and
> `logExplainQueryPlan` options in `createQuery` to measure and analyze
> performance.

For deeper insights into how SQLite indexes work under the hood, read [this in-depth guide](https://medium.com/@JasonWyatt/squeezing-performance-from-sqlite-indexes-indexes-c4e175f3c346).

## Usage

Using the schema, shared `run`, and `appOwner` from [Create Evolu](https://evolu.dev/docs/local-first#create-evolu):

```ts

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.

## Recommendations

SQLite offers a powerful [CLI tool](https://sqlite.org/cli.html#index_recommendations_sqlite_expert_) for index recommendations.

To use it:

1. Download the "Precompiled Binaries" [here](https://www.sqlite.org/download.html).
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.