SQL — Indexes

What is an index?

Database structure that improves query speed.

Create index

CREATE INDEX idx_users_email ON users(email);

Unique index

CREATE UNIQUE INDEX idx_users_email ON users(email);

Composite index

CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

Drop index

DROP INDEX idx_users_email;

View indexes

-- MySQL
SHOW INDEX FROM users;

-- PostgreSQL
SELECT * FROM pg_indexes WHERE tablename = 'users';

When to index

• Columns in WHERE clause
• Columns in JOIN conditions
• Columns in ORDER BY
• High-cardinality columns

When NOT to index

• Small tables
• Columns with low cardinality
• Frequently updated columns
• Tables with heavy writes

Performance impact

-- Without index: Full table scan
-- With index: Index scan (faster)

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Mini Practice

1. Create an index
2. Create composite index
3. Check query performance
4. Drop unused indexes

Up Next

Continue with Constraints - Table constraints.