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.
Performance
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 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 server of the cluster ticks once a minute; nothing is added to your requests.
What you get
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.
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).
Watch it
On the real admin panel, with real data.
Good to know
- Only successful calls that did not come from the cache are recorded.
- 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.
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.
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.