What are database indexes and how to create them

Short answer

An index is a data structure that speeds up row lookups at the cost of additional storage and slower writes. Create indexes with CREATE INDEX.

Steps

1. Create a single-column index: CREATE INDEX idx_last_name ON employees(last_name);
2. Create a composite index: CREATE INDEX idx_name ON employees(last_name, first_name);
3. Create a unique index: CREATE UNIQUE INDEX idx_email ON users(email);
4. Drop an index: DROP INDEX idx_name;
5. Analyze query plans with EXPLAIN to verify index usage.

Tips

• Index columns frequently used in WHERE, JOIN, ORDER BY, and GROUP BY clauses.
• Avoid over-indexing; each index adds overhead to inserts, updates, and deletes.

Common issues

• Indexes on low-cardinality columns (like boolean flags) rarely help and can hurt performance.
• Functions on indexed columns in WHERE prevent the optimizer from using the index.r