Fix slow queries
End a Slow query incident with Add index, clear the missing indexes that new Features ship, and stop N+1 queries from ORM frameworks.
Use this guide when a Slow query incident runs, or when the Database CPU climbs while traffic stays the same.
A query with no index scans the whole table. Its cost grows with the number of rows, and the rows grow with your users. So a missing index is invisible at first and becomes the bottleneck later.
Before you start#
You need Query analysis. It opens in the Garage Era. Without it, the game tells you that a query has no index, but you cannot add one.
End a Slow query incident#
Look for the message "A new query has no index. Every read scans the table." It appears when the incident starts.
Select the Database. The same message shows as a red callout at the top of the Inspector.
Click Add index in the callout. The Add index task goes to the top of the Team backlog, and the Team spends the Dev Hours it has on it at once.
Watch the line with the hammer icon at the bottom of the HUD. It names the work that the Team builds now.
When the task ships, the incident ends. Check that the Database Load row falls back.
If the callout says "Research Query analysis to find it", start that Research first. While you wait, a Cache in front of the Database keeps most reads off the table.
Find indexes before the incident#
Some Features ship query shapes with no index. Each open shape makes a Slow query more likely.
Press T to open the Team panel.
Find the Add index tile under Engineering. If it shows Queue, at least one query shape has no index.
Click Queue. Each Add index covers one shape.
Repeat until the tile no longer offers Queue. Then every query has an index.
Engineering tiles appear only for tasks whose Research you own.
Stop N+1 queries#
Laravel, Rails, and Django use an ORM that sends several read queries where one would do. This is the N+1 query pattern. The Database does several times the read work for the same traffic.
Check the Framework section of an App Server. If it names Laravel, Rails, or Django, the pattern applies.
Research Eager loading.
In the Team panel, queue the Eager loading task. It costs 10 Dev Hours.
When it ships, check that the Database CPU gauge falls.
The other choice is a framework with no N+1 queries, such as Symfony. That is a Stack migration and costs more Dev Hours. See Choose a Stack.
Keep reads off the Database#
An index makes each query cheaper. These Components send fewer queries to the Database:
A Cache answers repeated reads from memory.
A Read Replica answers reads with a copy of the data.
A Data Warehouse takes report scans, from the Series B Era.
See Scale the database for the steps.
Check the result#
Select the Database and open Resources. The CPU gauge must fall after each fix.
Check that no red callout remains at the top of the Inspector.
Check that Apdex on the HUD returns to its target.
Practice#
The Read Mostly Sandbox ends with a query that ships with no index. Keep Apdex above its limit for two minutes after it ships.
Related#
Team and velocity explains Dev Hours and the backlog.