Grouped by shape
status = "paid" and status = "new" are the same query. Paging, sorting and selected fields do not split a group either.
Extension · license key
Index Maker
See the queries your APIs run, find the ones that use no index, and create the index from the panel.
Index Maker is part of API Maker + Extensions. Start a scanning period and it records the database queries of your APIs, grouped by the shape of their condition. Then it asks your database, with explain, whether each query uses an index, and lists the ones that do not.
How it works
Start a scanning period.
Give it a name, a start and an end, and pick what to watch: the database APIs of every instance, or of some instances, databases and tables, and system APIs.
Use your app as usual.
Every successful call that did not come from the cache is recorded, grouped by API, table and the shape of its condition.
Run the index analysis.
For each query shape, Index Maker runs explain on the database: the query uses a full index, part of one, or none.
Create the missing index.
Queries without an index are listed as suggestions. Open the index form of that table from the list, pick the fields and create it.
What you get
Numbers for each query
How often it ran, rows returned, rows saved or updated and its time, with up to 100 samples that keep the real values.
Your database decides
The check is the explain of your own database, run with a recorded sample of the query. Nothing is guessed.
Suggestions
The queries that use no index, in one list, each with a button that opens the index form of its table.
One run at a time
Run a custom API from the API testing page and its Index Maker tab shows the queries of that run: their index use, a timeline of the calls and the suggestions.
Index options of each database
Create and remove indexes with the options your database has, like hashed and text indexes on MongoDB or gin and brin on PostgreSQL.
A log of index changes
Every index created or removed from API Maker is logged with its table, fields and date.
An example
The orders list got slow
Orders grew to millions of rows and the orders list now takes seconds. A one hour scanning period shows the query behind it, on customer_id and status, using no index. From the suggestion, the index is created on the orders table.
[ { "find": { "status": "paid", "customer_id": 42 }, "limit": 10 }, { "find": { "customer_id": 7, "status": "new" }, "sort": { "_id": -1 } }]Both are the shape find { customer_id, status }: they are counted together and checked once.
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. Run scanning periods when you need them, not all the time.
Questions
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.
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.
Are multi tenant APIs covered?
Yes. Queries are kept per tenant, and an index created from a suggestion goes to the database of that tenant.
What are internal queries?
Queries API Maker runs on its own while serving a call, like deep populate and unique checks. Turn on internal queries in the scanning period to record them too.
Learn more
Documentation