What Is an Index?

-- Create a basic index on a single column
CREATE INDEX idx_employees_last_name ON employees(last_name);

-- Query that benefits from the index
SELECT * FROM employees WHERE last_name = 'Smith';

How Indexes Work

Without an index, the database performs a full table scan — reading every row to find matches. With an index, it traverses the tree directly to the matching rows.

Types of Indexes

Single-Column Index

CREATE INDEX idx_employees_email ON employees(email);

Composite Index (Multi-Column)

-- Order matters: leftmost prefix rule
CREATE INDEX idx_employees_dept_salary ON employees(department_id, salary);

Unique Index

-- Enforces uniqueness and provides fast lookups
CREATE UNIQUE INDEX idx_employees_email ON employees(email);

Partial Index (PostgreSQL)

-- Index only active employees
CREATE INDEX idx_active_employees ON employees(last_name)
WHERE status = 'Active';

Covering Index

-- Include columns needed by the query to avoid table lookups
CREATE INDEX idx_emp_covering ON employees(department_id, salary, first_name, last_name);

-- This query is fully served by the index — no table access needed
SELECT first_name, last_name
FROM employees
WHERE department_id = 5 AND salary > 70000;

Expression Index

-- Index on a function or expression
CREATE INDEX idx_emp_lower_email ON employees(LOWER(email));

-- Uses the expression index
SELECT * FROM employees WHERE LOWER(email) = 'john@example.com';

Creating and Managing Indexes

-- Create index
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Create index only if it doesn't exist (PostgreSQL)
CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id);

-- Drop an index
DROP INDEX idx_orders_customer;

-- Rename an index (PostgreSQL)
ALTER INDEX idx_orders_customer RENAME TO idx_cust_orders;

-- List all indexes on a table (PostgreSQL)
SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'employees';

-- List indexes (MySQL)
SHOW INDEX FROM employees;

When to Create an Index

Index Impact on Performance

Index Monitoring

-- PostgreSQL: Check index usage statistics
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS times_used,
    idx_tup_read AS rows_read,
    idx_tup_fetch AS rows_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE tablename = 'employees'
ORDER BY idx_scan DESC;

-- PostgreSQL: Find unused indexes
SELECT
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Check if an index is being used (PostgreSQL)
EXPLAIN ANALYZE
SELECT * FROM employees WHERE last_name = 'Smith';
-- Look for "Index Scan" vs "Seq Scan" in the output

Common Index Patterns

Database-Specific Notes