PlanetScale’s query performance metrics are surprisingly good at telling you when a query is slow, but they’re even better at telling you why it’s slow in a way that lets you predict future performance issues.
Let’s see what that looks like. Imagine you’ve got a simple users table and you’re querying for users by email:
SELECT * FROM users WHERE email = 'test@example.com';
In PlanetScale, when you look at your database’s "Insights" tab, you’ll see a "Slow Queries" section. It might show something like this:
| Query | Average Latency | Rows Examined | Rows Returned |
|---|---|---|---|
| SELECT * FROM users WHERE email = ? | 150ms | 100,000 | 1 |
This tells you the query took 150 milliseconds, and it had to look at 100,000 rows to find the single row it needed. The "Rows Examined" versus "Rows Returned" is the first clue. If those numbers are vastly different, it’s a strong indicator that an index is missing or not being used effectively.
The underlying mechanism here is PlanetScale’s query analysis. When a query runs, PlanetScale doesn’t just time it; it records how many rows the database had to scan to satisfy the WHERE clause. For a SELECT * FROM users WHERE email = 'test@example.com', if there’s no index on the email column, the database has to perform a full table scan. It reads every single row in the users table and checks if the email column matches. This is why "Rows Examined" can be so high.
To fix this, you’d add an index:
ALTER TABLE users ADD INDEX idx_email (email);
After adding this index, the same query might look like this in PlanetScale’s insights:
| Query | Average Latency | Rows Examined | Rows Returned |
|---|---|---|---|
| SELECT * FROM users WHERE email = ? | 5ms | 1 | 1 |
Now, "Rows Examined" is 1. The index allows the database to directly locate the row with the matching email without scanning the whole table. The latency drops dramatically because the disk I/O and CPU work are significantly reduced.
PlanetScale’s metrics go deeper. You can also see "Query Time Breakdown." This shows you where the time is spent: "Locking," "Waiting," "Parsing," "Planning," and "Executing." For our indexed query, "Executing" will be very low. If you saw high "Locking" or "Waiting" times, it would point to contention issues – other queries holding locks on the rows or tables you need.
The real power comes from understanding the interplay. High "Rows Examined" coupled with high "Average Latency" is the classic "missing index" scenario. High "Rows Examined" but low "Average Latency" might mean the query is using an index, but the index itself is inefficient (e.g., a low-cardinality index).
Consider queries with JOIN operations. PlanetScale will show you the JOIN order and how many rows are examined at each step. If a JOIN involves a table scan on a large table, it can balloon the "Rows Examined" count for the entire query. The fix is often ensuring that the columns used in ON clauses are indexed on both tables involved in the join.
Another critical metric is "Rows Returned." If this is consistently much higher than your application expects, it could indicate a query that’s fetching more data than necessary. While not strictly a performance bottleneck in terms of database execution time, it increases network traffic and application processing overhead. For example, SELECT * when you only need a few columns. Changing it to SELECT id, name, email can reduce the amount of data transferred.
The most surprising thing about these metrics is how often a simple index on a column used in a WHERE clause can solve 90% of your performance problems, and how PlanetScale’s "Rows Examined" metric is the most direct indicator of this. It’s not just about how long a query takes, but how much work the database had to do. A query that examines 100,000 rows to find one is fundamentally different from one that examines 1 row. The latter is efficient; the former is a ticking time bomb.
The next thing you’ll likely encounter is optimizing queries that involve multiple conditions in the WHERE clause, and how PlanetScale’s metrics can help you understand if your composite indexes are being used effectively.