# Index Maker

> Record the database queries of your APIs during a scanning period, see which ones use no index with explain, and create the missing indexes.

Source: https://docs.apimaker.dev/v4/extensions/index_maker/introduction.html

- Index Maker records the database queries of your APIs, groups them by the shape of their condition, and asks your database with explain whether each one uses an index.
- It is in the **Index Maker** menu of the admin panel.
- Since v3.3.0 it can do all of it on its own : the [automatic mode](https://docs.apimaker.dev/v4/extensions/index_maker/automatic_indexes.html) records at regular intervals, creates the indexes the slow or frequent queries of big tables need, and writes them into the migration script `am_index_maker` so every environment gets them on deploy.

## 1. Start a scanning period

- Give it a name, a start and an end.
- Pick what to watch: the database APIs of every instance, or of some instances, databases and tables, and system APIs.
- Turn on internal queries to also record the queries API Maker runs on its own while serving a call, like deep populate and unique checks.

## 2. Use your app

- While the period runs, every watched call that succeeded and did not come from the cache is recorded.
- Calls are grouped by API, table, tenant and the shape of their condition: the `find` and `deep` of the query without their values.
- Paging, sorting and selected fields do not split a group.

```json
[
    { "find": { "status": "paid", "customer_id": 42 }, "limit": 10 },
    { "find": { "customer_id": 7, "status": "new" }, "sort": { "_id": -1 } }
]
```

- Both calls above are one query shape, `find { customer_id, status }`.
- For each shape you see how often it ran, the rows returned, the rows saved or updated and its time, with up to 100 samples that keep the real values.

## 3. Run the index analysis

- For each query shape, Index Maker runs explain on its database with a recorded sample of the query.
- Each shape is marked as using a full index, part of an index, or no index.
- Supported for MongoDB, MySQL, MariaDB, SQL Server, PostgreSQL and Oracle.

## 4. Create the missing indexes

- **Suggestions** lists the query shapes that use no index. Each one opens the index form of its table, and **Add to script** puts the selected shapes into the migration script `am_index_maker` in one go, with the fields chosen the way the automatic mode chooses them.
- The index form shows every field of the table with whether a plain index can hold it (and why not), the indexes the table already has and its size. The name is built from the fields, the same in every environment. Pick the fields and the options of your database, and create the index. For a multi-tenant API, the index goes to the database of the tenant of that query.
- **Also write it to the migration script** (on by default in the form) adds the index to `am_index_maker`, so every environment which deploys the repository creates it too.

| Database | Options |
|---|---|
| MongoDB | unique, ascending, descending, hashed, text |
| MySQL, MariaDB | FULLTEXT, SPATIAL, UNIQUE, with BTREE, HASH or RTREE |
| SQL Server | CLUSTERED, NONCLUSTERED, XML, SPATIAL, unique or not |
| PostgreSQL | btree, hash, gist, gin, spgist, brin, unique or not, NULLS FIRST or LAST |
| Oracle | unique, ASC, DESC |

- Every index created or removed from API Maker is logged with its table, fields, date and source : the form, a suggestion, the automatic mode, custom code (`g.sys.system.createIndexes`) or the migration script on deploy.

## Automatic mode

- Switch it on in the **Automatic indexes** panel and Index Maker starts its own scanning periods, evaluates them when they end and creates the indexes that pass its thresholds (table size, average time, number of calls). Everything it decides is written into the migration script `am_index_maker`, which can not be deleted, and every decision is kept on the period. See [Automatic indexes](https://docs.apimaker.dev/v4/extensions/index_maker/automatic_indexes.html).

## From your code

- `g.sys.system.createIndexes`, `g.sys.system.dropIndexes` and `g.sys.system.getIndexes` create, drop and list indexes on any database of the account, in the same words for every database : see the [examples](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-create-indexes-api.html#from-code).

## One run from the API testing page

- Run a custom API (or an event, a scheduler, a hook...) from the API testing page. Its **Index Maker** tab shows every database call of that run, with no scanning period to start.
- **Summary**: what matters most in one line, then the run in numbers: the calls and their query shapes, the time in the databases and its share of the request, the rows read and written, and how many calls an index served. **What to look at** lists, the most serious first, the queries that read a whole table, a query in a loop (one table asked many times with different values), the same query asked again, a slow call, a big read, and calls that ran one after another.
- **Timeline**: each call where it ran in the run, how long it took and how much of it the database took, how many calls ran at once, and the time before the first call. Click a call to see its query.
- **DB Calls**: every query shape with its index use, calls, time, rows and samples, filtered by index use or searched.
- **Suggestions**: the queries that use no index, the costliest first. Each one opens the index form filled from the query, which can also write the index into the migration script `am_index_maker`.
- The data of a run is read once, right after the run, and then removed.

## Good to know

- Each watched call adds a few writes to the database of API Maker, after its response. Run scanning periods when you need them, not all the time.
- Without Index Maker too, the index list of a table on the instances page shows, creates and removes indexes.

👉 Index Maker is part of API Maker : there is nothing more to install.