Your AI, on steroids. Shorter replies, tailored to you.

An index in SQL is a separate data structure that the database maintains alongside a table so it can find rows quickly without scanning the entire table. It works like the index at the back of a book: instead of reading every page to find a topic, you look it up in a sorted list that points you to the right page.

How it works

Without an index, a query like WHERE email = 'a@b.com' forces a full table scan: the database reads every row. With an index on email, the database searches a sorted structure (usually a B-tree) in logarithmic time, then jumps directly to the matching row(s).

Creating an index

CREATE INDEX idx_users_email ON users (email);

Unique index (also enforces no duplicates):

CREATE UNIQUE INDEX idx_users_email ON users (email);

Composite index (multiple columns):

CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

Dropping an index:

DROP INDEX idx_users_email;

Common types

• B-tree: the default in most databases; good for equality, ranges, and sorting.
• Hash: fast for exact equality lookups only.
• Full-text: for searching words within text.
• GIN / GiST (PostgreSQL): for arrays, JSON, geospatial data, etc.
• Clustered vs. non-clustered (SQL Server, MySQL/InnoDB): a clustered index determines the physical order of rows (one per table); non-clustered indexes are separate structures pointing back to the rows.

When indexes help

• Columns used in WHERE, JOIN, ORDER BY, or GROUP BY
• Foreign key columns
• Columns with high selectivity (many distinct values)

Trade-offs

• Slower writes: every INSERT, UPDATE, and DELETE must also update the indexes.
• Extra storage: each index takes disk space.
• Not always used: on tiny tables or low-selectivity columns (e.g., a boolean), the planner may ignore the index.
• Column order matters in composite indexes: an index on (user_id, created_at) helps queries filtering on user_id or on both, but generally not on created_at alone.

Checking usage

Use EXPLAIN (or EXPLAIN ANALYZE) to see whether a query uses an index:

EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';

Rule of thumb: index the columns you frequently search, join, or sort on, but don’t index everything.