Skip to content
This page View Markdown Open in ChatGPT Open in Claude

Index Maker

  • 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 API Maker + Extensions.
  • Since v3.3.0 it can do all of it on its own : the automatic mode 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.
1
2
3
4
[
    { "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.

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.

One run from the API testing page

  • Run a custom API from the API testing page. Its Index Maker tab shows the database queries of that run only, with no scanning period to start.
  • For each query: whether it used an index, how often it ran and its samples. A timeline shows the calls, and the queries that use no index are listed as suggestions.
  • 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 API Maker + Extensions, the index list of a table on the instances page still shows, creates and removes indexes.

👉 Index Maker comes with API Maker + Extensions, the package installed with a license key. For a license key, contact us at [email protected].