An index is a side structure that lets the database find rows without reading the whole table. The right index turns a slow list into an instant one. The wrong ones slow down writes and waste space.

​
Do you need one?

Add an index on a column when you filter or sort by it often on a table that will grow past a few thousand rows. Typical candidates: foreign-key columns (author_id), status, created_at, slug, email. Skip indexes on tiny tables, on columns you rarely query, and on columns with only a few distinct values by themselves (a boolean).

​
Level 1: a flag on a column

The simplest index is a flag. In Studio, open the table → Columns tab → click the column and switch Indexed or Unique on. Save and apply the migration. The column then looks like this in the definition:

​
Level 2: declared indexes

For several columns, or a condition, declare an index on the resource. In Studio: the table’s Indexes tab, Add index. A resource may declare up to 16 indexes of up to 8 columns each. Conditions are structured, never SQL strings: each is a column, an op and a value. Operators: =, !=, <, <=, >, >=, is_null, not_null.

​
Order matters

A composite index on (customer_id, created_at) helps queries that filter by customer_id, or by customer_id and created_at. It doesn’t help a query that filters only on created_at. Put the column you always filter on first, then the one you sort by.

​
Partial indexes

A partial index covers only a slice, so it is smaller and faster for the common case: “rows that are not deleted”, “open orders”. A uniqueness rule on a partial index applies only within the slice, which is how you say “one open cart per customer” without blocking closed ones.

​
See and manage them

The list shows four kinds: declared (in the definition), field (from indexed / unique), hand-made (created outside Pawabase), and system. In Studio the Indexes tab shows them with what each covers. dropIndex removes one made outside Pawabase; declared ones are removed by editing the definition. Dropping or rebuilding an index slows queries until it is back, and removing a unique index lifts the guarantee. The change can be rolled back. See Migrations and history.

​
Adding a unique index to existing data

If duplicates already exist, the index can’t be built. Find them first with the SQL editor:
Fix or remove the duplicates, then add the index.

​
How to tell an index is used

In the SQL editor, run Show how the database would run this query (explain) on your query. Look for an index scan rather than a full table scan. If a slow query isn’t using your index, check the column order and that the filter matches the index’s condition.

Filter

The queries indexes speed up.

SQL editor

Explain a query.