SQL Indexes

Overview

Indexes are data structures that accelerate lookups at the cost of extra storage and slower writes. Most databases use B-tree indexes by default. Index columns used in WHERE, JOIN, and ORDER BY clauses on large tables.

Syntax / Usage

-- Create index
CREATE INDEX idx_users_email ON users (email);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

-- Composite index (column order matters)
CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC);

-- Partial index (Postgres)
CREATE INDEX idx_active_users
ON users (last_login_at)
WHERE active = true;

-- Drop index
DROP INDEX idx_users_email;

Examples

Foreign key lookup:

CREATE INDEX idx_posts_author_id ON posts (author_id);
-- speeds: SELECT * FROM posts WHERE author_id = ?

Covering common query pattern:

-- Query: WHERE status = ? ORDER BY created_at DESC
CREATE INDEX idx_tasks_status_created
ON tasks (status, created_at DESC);

Common Mistakes

• Indexing every column—writes slow down, storage grows
• Wrong composite column order—index (created_at, status) won't help WHERE status = ? alone
• Low-cardinality columns alone (e.g. boolean) rarely help
• Not analyzing query plans (EXPLAIN) before adding indexes

See Also

select where-clause joins group-by