> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ditto.live/llms.txt
> Use this file to discover all available pages before exploring further.

# ADVISE

> DQL language syntax for recommending the indexes that would let a statement run more efficiently.

You can preface a DQL statement with ADVISE to analyse it and receive advice on which indexes would benefit it.  The statement is planned but never executed, and the result is a single document describing the indexes the planner recommends.

<Note>The statement isn't executed when using ADVISE, only the parsing and planning stages take place.  No documents are read or modified.</Note>

ADVISE is the mechanism the planner points you to when a query cannot run efficiently — or at all — without index support.  For example, a join whose inner leg has no index on its join key fails with an error that explicitly suggests running ADVISE on the query.

<img src="https://mintcdn.com/ditto-248bc0d1/4WlyZ5cmeURK3o9P/images/dql/Advise.svg?fit=max&auto=format&n=4WlyZ5cmeURK3o9P&q=85&s=e84f1a42c4333ae6d591f3010cca60a6" alt="ADVISE Syntax Diagram" width="467" height="109" data-path="images/dql/Advise.svg" />

## Which statements can be advised

ADVISE can preface most statements — anything except a nested [`EXPLAIN`](/dql/explain), [`PROFILE`](/dql/profile), or `ADVISE`.

Advice is only produced for statements that scan a collection:

* [`SELECT`](/dql/select)
* [`UPDATE`](/dql/update)
* [`DELETE`](/dql/delete), [`EVICT`](/dql/evict), and `TOMBSTONE`
* [`INSERT ... SELECT`](/dql/insert) — the advice is derived from the embedded `SELECT`

Any other statement — including an `INSERT` with literal `VALUES` — is accepted but returns an `outcome` of `no advice available for statement`.

## The advice document

The result is a single document with an `advice` field containing:

| Field              | Description                                                                                                                                                                                             |
| ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `statement`        | The statement that was analysed.                                                                                                                                                                        |
| `suggestedIndexes` | An array of recommended indexes.  Each entry has the target `collection`, a ready-to-run `CREATE INDEX IF NOT EXISTS` `statement`, and a `reason` describing which parts of the query the index serves. |
| `existingIndexes`  | Indexes already present that the planner considered for the statement, when any exist.  Each entry has a `collection` and the index's `CREATE INDEX` `statement`.                                       |
| `outcome`          | Present *instead of* `suggestedIndexes` when no advice can be given, e.g. `no keys to advise on`, `no advice available for statement`, or `collection <name> does not support indexing`.                |

Each recommendation combines the indexable clauses of a statement into as few composite indexes as possible.  Keys are ordered as equality keys first, then `ORDER BY` keys, then range keys, followed by projected fields when a covering index is worthwhile.  Aliased `ORDER BY` fields are resolved back to the underlying document field.

## Examples

A statement with nothing to index on returns an `outcome` rather than suggestions:

```sql DQL theme={null}
ADVISE SELECT * FROM atest
```

produces:

```text theme={null}
{
  "advice": {
    "outcome": "no keys to advise on",
    "statement": "SELECT * FROM atest\n"
  }
}
```

An equality predicate is advised as a single-key index:

```sql DQL theme={null}
ADVISE SELECT * FROM atest WHERE e=1
```

produces:

```text theme={null}
{
  "advice": {
    "statement": "SELECT * FROM atest WHERE e=1\n",
    "suggestedIndexes": [
      {
        "collection": "atest",
        "reason": "equality predicates on `e`",
        "statement": "CREATE INDEX IF NOT EXISTS adv_atest_e ON default:`atest` (`e` ASC)"
      }
    ]
  }
}
```

Equality and range predicates on the same collection are combined into one composite index, with the equality key ordered ahead of the range key:

```sql DQL theme={null}
ADVISE SELECT * FROM atest WHERE r>0 AND e=1
```

produces:

```text theme={null}
{
  "advice": {
    "statement": "SELECT * FROM atest WHERE r>0 AND e=1\n",
    "suggestedIndexes": [
      {
        "collection": "atest",
        "reason": "equality predicates on `e`; range predicates on `r`",
        "statement": "CREATE INDEX IF NOT EXISTS adv_atest_e_r ON default:`atest` (`e` ASC, `r` ASC)"
      }
    ]
  }
}
```

## ADVISE AND PROVISION

Adding the optional `AND PROVISION` clause makes ADVISE also create the suggested indexes as part of the same statement:

```sql DQL theme={null}
ADVISE AND PROVISION SELECT * FROM atest WHERE e=1
```

The recommendations that were created successfully are reported under `createdIndexes` (in place of `suggestedIndexes`):

```text theme={null}
{
  "advice": {
    "statement": "SELECT * FROM atest WHERE e=1\n",
    "createdIndexes": [
      {
        "collection": "atest",
        "reason": "equality predicates on `e`",
        "statement": "CREATE INDEX IF NOT EXISTS adv_atest_e ON default:`atest` (`e` ASC)"
      }
    ]
  }
}
```

Any index that could not be created is reported under `failedIndexes` instead, each entry carrying an `error` describing why creation failed.

<Note>ADVISE is currently supported on the small peer only.</Note>


## Related topics

- [SELECT](/dql/select.md)
- [Migration Guide for Atlas Device Sync](/sdk/latest/quickstarts/mongodb-migrationguide.md)
- [JavaScript Web Release Notes](/sdk/latest/release-notes/javascript-web.md)