🏗️ SchemaintermediateCommonly used

How to create and use indexes in SQL

Speed up queries by creating indexes on frequently queried columns.

Create a basic indexAll databases

CREATE INDEX idx_users_email ON users(email);

Speeds up queries that filter or sort by email. Name your indexes descriptively.

When to use: Columns frequently used in WHERE, JOIN ON, or ORDER BY.

Create a unique indexAll databases

CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

Enforces uniqueness and speeds up lookups. Equivalent to a UNIQUE constraint.

Create a composite indexAll databases

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Indexes multiple columns together. Speeds up queries that filter by both columns.

When to use: Queries that filter by multiple columns together.

Column order matters — this index helps WHERE user_id = ? AND status = ? but not WHERE status = ? alone.

Create index concurrently (PostgreSQL)PostgreSQL

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

Builds the index without locking the table. Slower but safe for production.

When to use: Adding indexes on large production tables without downtime.

List all indexes (PostgreSQL)PostgreSQL

SELECT
  indexname,
  tablename,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename;

Shows all indexes in the public schema.

Drop an indexAll databases

DROP INDEX idx_users_email;
-- PostgreSQL: won't lock the table:
DROP INDEX CONCURRENTLY idx_users_email;

Removes an index. Use CONCURRENTLY in PostgreSQL to avoid table lock.

Common Mistakes

→Over-indexing — every index slows down INSERT/UPDATE/DELETE. Only index what you query.
→Indexing low-cardinality columns (boolean, status with 3 values) — often not worth it.
→Not using CONCURRENTLY in production PostgreSQL — locks the table during index creation.