Skip to content

Automatic indexes

Automatic indexes (v3.3.0+)

  • Switch the automatic mode on and Index Maker works on its own : it records the database queries of your APIs at regular intervals, asks the database with explain which ones use no index, and gives an index to the queries that are slow or frequent on tables that are big enough.
  • Every index it decides on goes to two places : the database of the server that decided it (optional), and the migration script am_index_maker, so every other environment creates the same index on its next deploy.
  • It is in API Maker + Extensions, in the Index Maker menu : the Automatic indexes panel next to the scanning periods.

The panel

  • The switch turns the mode on and off for the account (an admin or a developer account has its own periods, script and settings).
  • Scan now starts an automatic scanning period at once instead of waiting for the next one.
  • Evaluate last period runs the evaluation of the last automatic period again.
  • Script opens the entries of am_index_maker : remove one, open the code, run the script on this server.
  • Run here runs the script on this server, like the play button of the migrations page.
  • The panel shows the period recording right now, when the next one is due, the last evaluation with its decisions, and how many indexes were written and created.

What it does, step by step

  1. Record. A scanning period named Automatic <date> records the database APIs of every instance for scanWindowMinutes. It costs what a scanning period costs : a few writes to the database of API Maker after each watched call, only while it records.
  2. Explain. When the period ends, every recorded query shape is run through explain on its database, like the index analysis of a scanning period. Only the shapes that use no index go on (includePartialIndexQueries adds the ones that use part of an index).
  3. Decide. For every shape, in the order of the queries that matter most (calls × time) :
    • the collection is not in excludedCollections ;
    • the query is worth it : slower than minAvgQueryTimeMS on average, or called at least minQueryCount times ;
    • the table is big enough : minTableRows rows or more. The size comes from the catalog and a count bounded by minTableRows, so a big table is never counted to its end ;
    • the fields of the index : the equality fields first, then the $in fields, then one range or pattern field, at most maxFieldsPerIndex. A field of a type the database can not index (TEXT and BLOB on MySQL, json on PostgreSQL, CLOB on Oracle, varchar(max) on SQL Server...) is dropped ;
    • no existing index of the table starts with those fields, the script does not hold them already, and the table stays under maxIndexesPerTable.
  4. Create. With applyOnThisServer, the index is created on the database of this server right away, with the name Index Maker gives it. An index the database refuses stays out of the script.
  5. Write. The definitions are written into the JSON list of am_index_maker, and the script is marked "not executed" on this server so that every environment runs it on its next deploy.
  6. Keep going. The next period starts scanEveryMinutes after the previous one ended. Old automatic periods are removed beyond keepAutomaticPeriods, with their recorded queries.

Every decision is kept on the period : open it in the scanning periods grid, tab Automatic indexes, to see for each query shape what was decided and why (APPLIED, WRITTEN, SMALL_TABLE, NOT_WORTH_IT, COVERED_BY_EXISTING_INDEX, ALREADY_IN_SCRIPT, TOO_MANY_INDEXES, NO_INDEXABLE_FIELD, UNSUPPORTED_SHAPE, EXCLUDED_COLLECTION, TABLE_NOT_READABLE, FAILED).

Settings

  • The settings are kept on the account (settings.indexMaker), so they travel with the project in git. A number outside its limits is brought back inside them, never refused.
Setting Default Limits Meaning
autoIndexEnabled false The master switch. Off, nothing runs on its own ; the script, the suggestions and the index form still work by hand.
applyOnThisServer true Besides the script, create the index on the database of this server as soon as it is decided.
scanEveryMinutes 360 5 to 43200 A new automatic period starts this long after the previous one ended.
scanWindowMinutes 30 1 to 1440 How long an automatic period records.
minTableRows 10000 0 to 10⁹ A table with fewer rows gets no index : a scan of a small table is cheap and every index costs on writes.
minAvgQueryTimeMS 100 0 to 600000 A query shape slower than this, on average, gets an index.
minQueryCount 50 1 to 10⁸ A query shape called at least this often during the period gets an index even when it is fast : the table grows.
maxFieldsPerIndex 3 1 to 8 Fields of a compound index : the equality fields first, then the ranges, the rest is dropped.
maxIndexesPerTable 12 1 to 64 A table that already carries this many indexes gets no more.
includePartialIndexQueries false Also look at the shapes that use part of an index, not only the ones without any.
keepAutomaticPeriods 3 1 to 50 Older automatic periods, with their recorded queries, are removed.
excludedCollections [] instance:database:collection patterns, * for any part. For a multi-tenant instance, the plain name of the instance.
logInternalQueries false Also record the queries API Maker runs on its own (deep populate, unique checks).

The migration script am_index_maker

  • It is a normal migration script of the account, on the Databases migration page, created the first time the mode is switched on (or the first time an entry is added by hand). It can not be deleted while Index Maker uses it ; remove its entries from the Index Maker page instead.
  • Index Maker only rewrites the JSON between the two markers. Edit the code outside them as you like, or add your own entries to the list (keep the JSON valid).
  • Every environment that pulls the repository runs the script on deploy. An index which exists already is reported as EXISTS and left alone, so the script can run again and again. A failed index is reported to the deploy after every other index of the list was created.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
import * as T from 'types';

// <am-index-maker-indexes>
const INDEXES: T.IIndexMakerScriptEntry[] = [
    {
        "instance": "mysql8",
        "database": "inventory",
        "collection": "orders",
        "name": "am_ix_orders_customer_id_status_3f9a1c",
        "fields": [{ "name": "customer_id" }, { "name": "status" }],
        "operation": "CREATE",
        "source": "AUTOMATIC",
        "reason": "Called 1,240 times, 310 ms on average, orders has 2,400,000 rows.",
        "addedAt": "2026-09-29T10:15:00.000Z",
        "scanningPeriodId": "6abbacb0bc471602b5f71099"
    }
];
// </am-index-maker-indexes>

async function main(g: T.IAMGlobal) {
    const creates = INDEXES.filter(e => (e.operation || 'CREATE') === 'CREATE');
    const results = await g.sys.system.createIndexes({
        indexes: creates,
        ifNotExists: true,
        continueOnError: true,
        skipUnindexable: true,
        source: 'MIGRATION_SCRIPT',
    });
    // ... DROP entries go through g.sys.system.dropIndexes, table by table
}
module.exports = main;
Entry field Meaning
instance Instance name. crm::acme names the tenant acme of the multi-tenant instance crm.
database Database name, as the account sees it (database name masking is applied).
collection or table The table. PostgreSQL : public.orders, SQL Server : dbo.orders.
name Index name. Missing : Index Maker builds one (see below).
fields [{ "name": "status", "order": "ASC" }]. Orders : ASC, DESC, HASHED (MongoDB), TEXT (MongoDB) ; nullsFirst: true on PostgreSQL.
unique A unique index.
type MySQL : FULLTEXT, SPATIAL ; SQL Server : CLUSTERED, XML, SPATIAL ; PostgreSQL : hash, gin, gist, spgist, brin. Plain when missing.
method MySQL : BTREE, HASH, RTREE.
operation CREATE (default) or DROP (the name is enough).
source, reason, addedAt, scanningPeriodId Who added the entry and why ; for people, not for the database.

Index names

  • am_ix_<table>_<fields>_<hash>, am_ixu_ for a unique index. The same fields give the same name in every environment, so EXISTS is found by name first and by fields second.
  • The name is cut to the shortest limit of the database : 100 characters on MongoDB, 64 on MySQL and MariaDB, 128 on SQL Server, 63 on PostgreSQL, 30 on Oracle. The hash keeps cut names apart.

By hand : suggestions and the index form

  • Suggestions of a scanning period : select the shapes and click Add to script. Index Maker turns each shape into a definition the way the automatic mode does (without the thresholds : you chose them) and adds it to the script.
  • 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 switch Also write it to the migration script (on by default) adds the index to am_index_maker after creating it.
  • Index logs keep who created or dropped every index : the form (UI), a suggestion (SUGGESTION), the automatic mode (AUTOMATIC), custom code (SYSTEM_API) or the script on deploy (MIGRATION_SCRIPT), with the reason and the scanning period.

From your code

  • The same three operations are system APIs, for custom APIs, schedulers and your own migration scripts : createIndexes, dropIndexes and getIndexes. They are also HTTP APIs : POST /api/system-api/user-path/create-indexes, /drop-indexes, /get-indexes.
  • createIndexes and dropIndexes change the database : the API Security Report lists them with the other sensitive system APIs, so give them only to the groups that need them.

Safe on every version

  • The statements Index Maker runs are the plain CREATE INDEX and DROP INDEX of each database, without IF NOT EXISTS (PostgreSQL 9.5+ only, MySQL never) : the existence check is made in code from the catalog, so the same script works on every version API Maker supports.
  • Identifiers are quoted on Oracle ("INVENTORY"."am_ix_...") and bracketed on SQL Server ([dbo].[orders]), so tables with lower-case or mixed-case names work.
  • A field the database can not put in a plain index is SKIPPED before the database is asked, with the reason : TEXT and BLOB (a prefix is needed) and JSON on MySQL and MariaDB ; text, ntext, image, xml, the spatial types, (n)varchar(max), varbinary(max) and keys over 900 bytes on SQL Server ; json, xml, the geometric types, tsvector on PostgreSQL ; LOBs, LONG, XMLTYPE and SDO_GEOMETRY on Oracle ; a primary key everywhere.

Performance

  • Nothing changes for a request : the only cost is the recording of a scanning period while one runs, which is the cost Index Maker always had.
  • The loop wakes once a minute in every process, but one process of the whole cluster does the work (a Redis claim of the minute), and its tick is one small read of the accounts with the mode on.
  • The evaluation runs once per period, at its end, and waits for a quieter minute when the event loop of the process is busy. It reads the catalog of the tables, never the rows beyond minTableRows.

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