Skip to content

Introduction

Index Maker (v1.20.0+)

  • 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.

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.
  • 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.
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 and date.

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 the extension, the index list of a table on the instances page still shows, creates and removes indexes.

👉 To install API Maker with extensions, follow API Maker + Extensions. For a license key, contact us at [email protected].