PlanetScale Query Optimization: Speed Up Slow Queries (2026)

PlanetScale’s query optimizer is a black box that can feel like magic, but understanding its core principles unlocks massive performance gains.

Let’s see it in action. Imagine a users table with millions of rows, and you’re trying to find active users who signed up in the last month and are in a specific region.

SELECT *
FROM users
WHERE signup_date >= '2023-10-01'
  AND is_active = TRUE
  AND region = 'USA';

Without proper indexing, this query might scan the entire users table, which is a slow, full table scan.

PlanetScale’s query optimizer, powered by Vitess under the hood, is designed to avoid these full table scans by leveraging indexes. When you execute a query, the optimizer analyzes it and consults the available indexes on your tables. It then chooses the most efficient "query plan" – the sequence of operations to retrieve the data.

The goal is to find a plan that minimizes disk I/O and CPU usage. This usually means using indexes to pinpoint the exact rows needed, rather than sifting through everything.

Here’s how it works internally. When a query arrives, Vitess’s query planner (which PlanetScale uses) does the following:

Parsing: The SQL query is parsed into an abstract syntax tree (AST).
Schema Analysis: The optimizer looks at the schema of the tables involved, including existing indexes.
Cost Estimation: For each possible way to execute the query (e.g., using different indexes, or a full table scan), the optimizer estimates the "cost." This cost is a theoretical measure based on factors like the number of rows expected to be read from disk and the number of CPU operations.
Plan Selection: The optimizer selects the query plan with the lowest estimated cost.
Execution: The chosen plan is executed.

The most crucial lever you control is indexing. Indexes are like the index at the back of a book; they allow the database to quickly find specific data without reading the whole book.

Consider our users table again. If we create an index on signup_date, is_active, and region, the optimizer can use it.

CREATE INDEX idx_user_filters ON users (signup_date, is_active, region);

Now, when the query runs, the optimizer sees this index and can efficiently locate users matching all three criteria. It traverses the index, which is a much smaller and more ordered structure than the main table, to find the matching rows.

Here are the key levers you can pull:

Composite Indexes: Indexes that cover multiple columns are incredibly powerful. The order of columns in a composite index matters. For our query, (signup_date, is_active, region) is good because the WHERE clause filters on all these columns. If you often query by region alone, you might consider (region, signup_date, is_active).
Index Selectivity: An index is more selective if it narrows down the results significantly. An index on is_active might not be very selective if 99% of your users are active. Combining it with a more selective column like signup_date or region in a composite index improves the overall selectivity of the index for that query.
Covering Indexes: If an index contains all the columns needed for a query (both in the WHERE clause and the SELECT list), the database might not even need to touch the main table. This is called a "covering index" and is the fastest path. For SELECT id, email FROM users WHERE signup_date >= '2023-10-01', an index on (signup_date, id, email) would be a covering index.
Query Rewriting: Sometimes, the optimizer can’t magically fix a poorly written query. Rewriting it to be more explicit or to use different constructs can help. For instance, avoiding OR conditions where possible, or ensuring that functions applied to indexed columns are avoided (e.g., DATE(signup_date) = '2023-10-01' prevents index usage on signup_date; signup_date >= '2023-10-01' AND signup_date < '2023-11-01' is better).
EXPLAIN: The most critical tool for understanding the optimizer’s choices is EXPLAIN. Running EXPLAIN SELECT ... before your slow query will show you the chosen query plan, including which indexes are used (or not used) and why. This is how you diagnose issues.

When you have a composite index like (signup_date, is_active, region), the query optimizer can efficiently use it for queries that filter on signup_date alone, signup_date and is_active, or all three columns. It can also use it for queries that filter on signup_date and region if is_active is also specified in the WHERE clause, even if is_active isn’t the leading column. The key is that the leading columns of the index must be used in the WHERE clause in the order they appear in the index definition, or the optimizer might fall back to a less efficient plan.

The next step after optimizing individual queries is understanding how PlanetScale’s distributed nature impacts performance, particularly with sharding and cross-shard queries.