Tools

Database Index Advisor

Paste a SELECT query and get the CREATE INDEX statements it argues for, read off the WHERE , JOIN ON , ORDER BY and GROUP BY columns — each with a cardinality guess, so you can tell the indexes worth building from the ones the planner will ignore.

SQL SELECT query

Try

Found 5 index recommendations across 2 tables.

Indexes

Tables

Composite

Multi-column

Low cardinality

Leading column

users2 indexes

WHEREsingleusers (is_active)

CREATE INDEX idx_users_is_active ON users (is_active);

Column used in WHERE clause — indexes speed up row filtering

is_activelow

The leading column looks low-cardinality. On its own this index is likely to be skipped by the planner — lead with a selective column instead, and keep this one as a trailing member of a composite.

O(log n) lookup vs O(n) full scan

JOIN ONsingleusers (id)

CREATE INDEX idx_users_id ON users (id);

JOIN ON condition — indexes on join keys eliminate nested-loop scans

idhigh

O(log n) vs O(n*m) without index

orders3 indexes

WHEREsingleorders (status)

CREATE INDEX idx_orders_status ON orders (status);

Column used in WHERE clause — indexes speed up row filtering

statuslow

The leading column looks low-cardinality. On its own this index is likely to be skipped by the planner — lead with a selective column instead, and keep this one as a trailing member of a composite.

O(log n) lookup vs O(n) full scan

JOIN ONsingleorders (user_id)

CREATE INDEX idx_orders_user_id ON orders (user_id);

JOIN ON condition — indexes on join keys eliminate nested-loop scans

user_idhigh

O(log n) vs O(n*m) without index

ORDER BYsingleorders (created_at)

CREATE INDEX idx_orders_created_at ON orders (created_at);

Column used in ORDER BY — index eliminates filesort

created_athigh

O(1) sort vs O(n log n) filesort

Migration script

CREATE INDEX idx_users_is_active ON users (is_active);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_users_id ON users (id);
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);

Every index costs write throughput and disk. Add them one at a time and confirm with EXPLAIN that the planner picks each one up — these are candidates, not a shopping list.

Cardinality guideRules of thumb behind the cardinality labels

High

Many distinct values — user_id, email, uuid, created_at. An index here narrows the search hard, which is what makes it worth the write cost.

Medium

Depends on the data — name, city, price. Check the real distinct count against the row count before committing.

Low

A handful of values — status, is_active, role. Alone the planner usually prefers a table scan; put them after a selective column in a composite.