Index Maker: Find Slow Queries and Create the Right Index, 6 Databases

Intro

0:00 Index Maker watches the database queries of your APIs, tells you which ones read a whole table, and gives them the right index, on six database engines.

Slow queries are found too late

0:10 Most teams find a missing index when production is already slow. Then someone turns on the slow query log of one database, reads explain plans in six different formats, adds an index by guess, and forgets it in the next environment. New queries, written by people or by AI assistants, keep coming. With API Maker, a scanning period records the real traffic of your APIs, every engine answers with the same verdict, and the index is created from the suggestion, with a name that is the same everywhere.

0:44 In this video : a slow API, a scanning period, what it records, the index check on every engine, the suggestion and the index form, and the same call, fast.

A slow API

0:56 Calm Home keeps the history of every order in sales dot order events, a MySQL table of one million rows. The order tracking page asks for the events of one request id. Nobody added an index on request id : the table is read from the first row to the last, for one answer. In API Testing, Get all of order events, with a find on one request id. Send.

1:23 One row comes back, and the bar shows the time : hundreds of milliseconds, for one row, on every call. With traffic, that is seconds of database time every minute.

One run in API Testing

1:34 Behind that page is a custom API, Order Tracking. Send it from API Testing, and open its Index Maker tab : every database call of this one run. Eight calls on five tables, with their time, their rows, and how many used an index. What matters most comes first : order events reads the whole table. It also finds a query in a loop : products, asked four times, once per item.

2:03 One query with dollar in gets them in one call. The timeline shows when each call ran : order events takes most of the run, while every other call takes a few milliseconds. Suggestions names the index it needs, on request id. That is one run of one API. To see every API under real traffic, record a scanning period.

Scanning period

2:26 Index Maker is in the Database menu. The page has two cards. Automatic indexes, on the left, runs Index Maker on its own : it has its own video. Scanning periods, on the right, is where you record on purpose. A scanning period is a window of time in which API Maker records the database queries of your APIs. Add new, give it a name, start it now, and in Run for, pick ten minutes.

2:55 Save. No slow query log to turn on, no profiler, no restart. The chip says it is recording, and when it ends. By default, a period watches every instance API and the system APIs that query a database. Pick a category, and the panes below narrow it : untick all instances, then pick mysql, sales, and order events. Save the scope with the icon of the row.

What it records

3:24 While the period records, the shop runs as usual. Order tracking asks MySQL by request id. Product pages ask MongoDB by SKU. Category pages ask MongoDB by category. The account page asks PostgreSQL by email. Calls that differ only in their values are one query shape : forty calls with forty request ids are one shape, with its calls, rows and time. Up to a hundred samples keep the real values.

3:55 Cached answers and failed calls are not recorded.

Index check

3:59 The period has data. Select it, and view the analysis. Check index usage asks every database, with its own explain, whether each shape uses an index. The summary answers : the queries recorded, the query shapes, the time spent reading whole tables, and the slowest shape. The ring shows how much of the work an index served. Each engine explains a query in its own words. MongoDB says index scan or collection scan.

4:30 MySQL and MariaDB name the key of the plan. SQL Server says seek or scan. PostgreSQL is asked with sequential scans switched off, so an index that can serve is used. Oracle prints its plan lines. Index Maker turns all of them into one verdict : full, partial or none. Below the summary, where an index helps most ranks the shapes without an index by what they cost : calls times average time.

4:59 Order events by request id comes first, by far. DB Queries lists every shape the period recorded, with its operation, its table, its verdict, its calls and its time. The eye opens the samples : forty calls of order events, each with its own request id, each reading the whole table.

Suggestions

5:20 Suggestions keeps the shapes that read a whole table, the most costly first. Each one offers three ways : create the index now, on this server ; add it to the migration script, so every environment creates it on deploy ; or let Index Maker decide. Add to script asks first. The script is the subject of the next video, so not now.

The index form

5:43 Create index opens the index form, already filled from the recorded query. Request id is marked in query, and selected. The name is built from the table and the fields : the same name in every environment. On the right : the size of the table, its existing indexes, and the fields no plain index can hold, with the reason. The options follow the engine : index type and method for MySQL, unique, hashed or text for MongoDB.

6:12 We leave the script for the next video, and create the index. Check index usage again. The time spent reading whole tables drops to almost nothing, and the coverage ring grows. The recorded times stay what they were : they are the history of the period. In DB Queries, order events now says full : the index serves the query from start to end.

Fast again

6:35 Back in API Testing, the same call, the same request id. The same row comes back, now in milliseconds. Nothing changed in the API, the code or the app : one index, on the right column. And Order Tracking? Sent again, its Index Maker tab has one thing left to look at : the products, asked one by one. On the timeline, order events is now a short bar, like the others.

7:05 Every index Index Maker creates is written to its log : the operation, the table, the name, the fields, where it came from, and when. And on the Instance API page, the index list of order events shows it next to the primary key. From there you can also create or drop indexes of any table, on any engine.

Index names

7:26 The name is deterministic : am ix, the table, the fields, and a short hash of both. Created by hand, from a suggestion or by the automatic mode, on development or production, the same index has the same name, cut to the limit of each database. So no environment ends up with two copies of one index.

Best practices

7:47 Best practices. Record during real traffic, not on an empty server. Check index usage after every release that adds queries. Index the costliest shape first, not every suggestion. Switch on log internal queries when deep populate or unique checks are involved. And keep the indexes in the migration script, so every environment gets them.

Recap

8:12 That was Index Maker. A scanning period records the real queries of your APIs. One verdict on six engines. Suggestions, the most costly first. An index from the form, with one name everywhere. Next, Index Maker on its own : automatic indexes. API Maker. Build faster. Spend smarter. Deliver better.