Automatic mode
Thresholds for table size, average time and number of calls, fields per index, indexes per table, excluded collections, how often and how long to record: settings of the account, kept in git.
Extension · license key
Index Maker
Records the queries your APIs run, finds the ones that use no index, and creates the index: on its own, on every environment.
Index Maker is part of API Maker + Extensions. It records the database queries of your APIs, grouped by the shape of their condition, and asks your database with explain whether each one uses an index. Switch on the automatic mode and it starts its own scanning periods, gives an index to the slow or frequent queries of big tables, and writes every index into the migration script am_index_maker, which every environment runs on deploy.
See it live
Step through it, slow it down, or open the full canvas.
api-maker/features/index-makerLive- Shapes
- 3recorded
- No index
- 2shapes
- Indexes
- 1written
- orders query
- 4ms
A period records on its own. Every six hours the loop starts a scanning period of thirty minutes. The queries of your APIs are grouped by the shape of their condition, with their calls and their time.
How it works
A period records on its own.
Every six hours the loop starts a scanning period of thirty minutes. The queries of your APIs are grouped by the shape of their condition, with their calls and their time.
Your database says which ones use no index.
When the period ends, every shape is run through explain with a recorded sample. The orders query reads the whole table.
The picker weighs each shape against your thresholds.
Slow or frequent, on a table big enough, not covered by an existing index, under the limit of indexes per table. Every decision is kept with its reason.
The index goes into am_index_maker as JSON.
A migration script that always exists and can not be deleted. Index Maker rewrites only the list between the markers; the code applies it.
Created here, created everywhere.
The index is created on this server at once. Every other environment runs the script on its next deploy: an index which exists already is left alone, so the script can run again and again.
The query is fast, the loop keeps watching.
The orders query drops from 310 ms to 4 ms. One process of the cluster ticks once a minute; nothing is added to your requests.
What you get
One script for every environment
am_index_maker holds every index as a JSON entry with who added it and why. Deploy the repository and the environment creates the same indexes. Add your own entries too.
Grouped by shape
status = "paid" and status = "new" are the same query. Paging, sorting and selected fields do not split a group either.
Your database decides
The check is the explain of your own database, run with a recorded sample of the query. Nothing is guessed.
Every decision explained
WRITTEN, SMALL_TABLE, NOT_WORTH_IT, COVERED_BY_EXISTING_INDEX, TOO_MANY_INDEXES, NO_INDEXABLE_FIELD... each shape of a period shows what was decided and why.
Safe on every version
Plain CREATE INDEX statements, the existence check made in code, quoted identifiers, names within the limit of each database, and fields no plain index can hold are skipped with the reason.
Index system APIs
g.sys.system.createIndexes, dropIndexes and getIndexes work the same on all six databases, from custom APIs, schedulers and your own migration scripts.
Suggestions and the index form
Select the shapes without an index and add them to the script in one click. The index form shows every field with whether an index can hold it, the existing indexes and the size of the table.
A log of index changes
Every index created or removed is logged with its table, fields, date and source: the form, a suggestion, the automatic mode, custom code or the script on deploy.
An example
The orders list got slow, and nobody had to notice
Orders grew to millions of rows. The automatic period of the night records the orders query on customer_id and status: 1,240 calls, 310 ms on average, no index. It passes the thresholds, so the index is created on the production database and written into am_index_maker. The staging and the development environments get it on their next deploy, and the query takes 4 ms in the morning.
// <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" }], "source": "AUTOMATIC", "reason": "1,240 calls, 310 ms on average, orders has 2,400,000 rows." }];// </am-index-maker-indexes>Index Maker rewrites only the list between the markers. The code of the script calls g.sys.system.createIndexes with ifNotExists, so it runs on every deploy without harm.
const results = await g.sys.system.createIndexes({ indexes: [{ instance: 'mysql8', database: 'inventory', table: 'orders', fields: [{ name: 'customer_id' }, { name: 'status', order: 'DESC' }], }],});// [{ status: 'CREATED', name: 'am_ix_orders_customer_id_status_3f9a1c', ... }]// the second time : [{ status: 'EXISTS', ... }]CREATED, EXISTS, SKIPPED (a field no plain index can hold, with the reason) or FAILED (the message of the database).
Good to know
- The Index Maker screen comes with API Maker + Extensions: installing it needs a license key.
- Only successful calls that did not come from the cache are recorded.
- Each watched call adds a few writes to the database of API Maker after its response, while a period records. The automatic mode records for a window every few hours, not all the time.
- The automatic mode creates indexes, it never drops one. A DROP entry can be added to the script by hand.
- Shapes no single index serves ($or, $nor, $where, $text, $expr) are reported, not indexed.
Questions
Which queries get an index automatically?
The shapes that use no index (part of one, if you ask), on a table with at least minTableRows rows, when the shape is slower than minAvgQueryTimeMS on average or was called at least minQueryCount times during the period. The fields are the equalities first, then the ranges, up to maxFieldsPerIndex. The defaults are 10,000 rows, 100 ms and 50 calls.
How do the other environments get the index?
Through the migration script am_index_maker, kept in your repository like every other migration script. Every change marks it "not executed" on the server that made it, so every environment runs it on its next deploy. An index which exists already is reported as EXISTS and left alone.
Can I edit the script?
Yes. Index Maker only rewrites the JSON list between its two markers. Edit the code outside them, or add your own entries to the list, including DROP entries. The script can not be deleted while Index Maker uses it; remove its entries from the Index Maker page instead.
What does it cost at run time?
Nothing per request. A scanning period costs a few writes to the database of API Maker after each watched call, while it records. The loop ticks once a minute in one process of the cluster, evaluates a period once when it ends, and waits for a quieter minute when the event loop is busy. Table sizes come from the catalog and a count bounded by the threshold.
Which index options are there?
MongoDB: unique, ascending, descending, hashed and text. MySQL and MariaDB: FULLTEXT, SPATIAL and UNIQUE, with BTREE, HASH or RTREE. SQL Server: clustered, nonclustered, XML and spatial, unique or not. PostgreSQL: btree, hash, gist, gin, spgist and brin, with NULLS FIRST or LAST. Oracle: unique, ASC and DESC.
Are multi tenant APIs covered?
Yes. Queries are kept per tenant, and an index goes to the database of that tenant: the entry names it as crm::acme.
Can I create indexes without the extension?
Yes. The index list of a table, on the instances page, shows its indexes and creates and removes them, and the system APIs createIndexes, dropIndexes and getIndexes are part of API Maker.