SQL Index Optimizer
Paste a SELECT and get CREATE INDEX candidates built from its WHERE, JOIN and ORDER BY columns, with equality columns first. A heuristic to confirm with EXPLAIN.
The interactive tool loads here when JavaScript runs.
About this toolWhat is the SQL Index Optimizer?
The SQL Index Optimizer reads a query and proposes indexes for the tables it touches. It resolves table aliases, finds columns compared with = or IN (equality), columns compared with <, >, BETWEEN or a prefix LIKE (range), join columns from ON clauses, and the ORDER BY or GROUP BY columns. For each table it writes one composite CREATE INDEX statement with a short reason.
Column order follows the rule most database engineers use for B-tree indexes: equality columns first, then the sort columns, or a single range column if there is no sort. An index on (customer_id, status, created_at) can jump straight to one customer's paid orders and read them already sorted by date.
This is a heuristic that works on the query text. It does not know which indexes you already have, how selective each column is, or how big the tables are. Treat every suggestion as a candidate: check it against existing indexes, create it on a staging copy, and compare EXPLAIN before and after.
- Resolves table aliases across FROM and JOIN
- Equality, join, sort and range columns in a sensible order
- One composite CREATE INDEX per table with a stated reason
- Skips primary key id columns that are already indexed
- Copy with reasons as SQL comments
- Runs in your browser; your query is not uploaded
Updated
How to use the SQL Index Optimizer
- Paste a SELECT statement into the SQL Query box.
- Click Analyze Query.
- Read each suggested CREATE INDEX statement and the reason under it: which equality, join, sort or range columns it covers.
- Click Copy to copy all suggestions with their reasons as SQL comments, then test them with EXPLAIN on a copy of your data.
Worked examples
Equality filters plus a sort
Input
SELECT id, total FROM orders WHERE customer_id = 42 AND status = 'paid' AND created_at >= '2026-01-01' ORDER BY created_at DESC LIMIT 20
Output
CREATE INDEX idx_orders_customer_id_status_created_at ON orders (customer_id, status, created_at);
customer_id and status are equality columns, so they lead. created_at is both the sort column and a range filter; as the sort column it follows the equality columns, and the database walks the index backwards for DESC.
A join with a filter on each side
Input
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 'active' ORDER BY o.created_at DESC
Output
CREATE INDEX idx_users_status ON users (status); CREATE INDEX idx_orders_user_id_created_at ON orders (user_id, created_at);
users.id is the primary key and already indexed, so only orders.user_id is treated as a join column. An index on status alone is only useful if active users are a small share of the table.
Background and insight
A B-tree index is sorted by its first column, then by the second within equal values of the first, and so on, like a phone book sorted by last name then first name. That is why column order matters. The index can be used for any leftmost prefix of its columns: an index on (a, b, c) helps WHERE a = ?, WHERE a = ? AND b = ?, and WHERE a = ? ORDER BY b, but not WHERE b = ? on its own.
Once the index hits a range condition, the columns after it are no longer in a useful order. With WHERE a = ? AND b > ? ORDER BY c, an index on (a, b, c) finds the rows quickly but they come out sorted by b, so the database still sorts by c. That is why the optimizer puts sort columns before a range column and tells you when it had to leave a range filter out: an index can serve the sort or the range, not both.
Every index has a price. It takes disk space and memory, and every INSERT, UPDATE and DELETE on the table has to maintain it. On a write-heavy table, five overlapping indexes can cost more than the reads they speed up. Primary keys are already indexed, which is why join columns named id are not suggested.
Common use cases
Fixing a slow query
Get a first candidate index for a query that shows a Seq Scan or filesort in its plan.
Reviewing new queries
Check whether a query in a pull request needs a supporting index before it ships.
Learning composite index order
Try variations of a WHERE and ORDER BY and see how the suggested column order changes.
Writing migrations
Copy the CREATE INDEX statement as the starting point for a migration file.
Tips and limitations
- Check existing indexes first: \d table in psql, SHOW INDEX FROM table in MySQL. An index on (a, b) already covers queries that need (a).
- Low-selectivity columns such as a boolean or a status with three values rarely make good leading columns on their own.
- On PostgreSQL, build indexes on busy tables with CREATE INDEX CONCURRENTLY to avoid blocking writes.
- Leading-wildcard LIKE patterns and functions on columns are ignored; they need trigram or expression indexes.
- Only the main query is read. Correlated subqueries and CTEs may need their own indexes.
Sources and further reading
- Multicolumn Indexes, PostgreSQL Global Development Group. Why leading columns matter in a B-tree index
- The right column order in multi-column indexes, Use The Index, Luke (Markus Winand). Leftmost-prefix rule explained
- Indexing SQL range conditions, Use The Index, Luke (Markus Winand). Why columns after a range condition stop helping
- Indexed ORDER BY, Use The Index, Luke (Markus Winand). When an index can serve the sort
- Query Planning, SQLite. How a database uses indexes, and their cost
Frequently asked questions
What order should columns go in a composite index?
Equality columns first, then the columns you sort by, then at most one range column. The index is sorted by its first column, then the next, so the database can jump to the equal values and read the rest in order. Columns after a range condition stop helping with sorting.
Why does the optimizer leave out my range filter?
When a query has both a range filter and an ORDER BY on a different column, one B-tree index can either narrow the range or return rows in sorted order, not both. The tool favours the sort, which usually matters more with LIMIT, and says so in the reason.
Does the SQL Index Optimizer know about my existing indexes?
No. It reads only the query text. Compare the suggestions with what SHOW INDEX (MySQL) or \d table (PostgreSQL) lists, because an existing index that starts with the same columns may already serve the query.
Can adding an index make things slower?
Yes, for writes. Every INSERT, UPDATE and DELETE must update every index on the table, and each index uses disk and memory. Add indexes for queries that matter, and drop ones that monitoring shows are never used.
Why is no index suggested for my query?
The query probably has no WHERE, JOIN ... ON or ORDER BY columns the tool can tie to a table, or it only filters with patterns an index cannot use, such as LIKE '%term' or a function around the column. Unqualified columns are only resolved when the query has a single table.
Is there an in-depth guide to the SQL Index Optimizer?
Yes. “SQL Indexes and EXPLAIN Plans: Finding Why a Query Is Slow” covers the background, how it works, common pitfalls and worked examples. Read the full guide.
Related Tools You May Like
Free, in-browser tools that pair well with this one.