Database Indexing Explained

What is Database Indexing?

An index is a data structure that improves query speed at the cost of additional writes and storage.

Index Types

• B-Tree: Most common, good for range queries
• Hash: Fast equality lookups
• Full-Text: Text search
• Composite: Multiple columns

Creating Indexes

-- Single column
CREATE INDEX idx_user_email ON users(email);

-- Composite index
CREATE INDEX idx_user_name_email ON users(name, email);

-- Unique index
CREATE UNIQUE INDEX idx_user_username ON users(username);

When to Use Indexes

• ✅ Columns in WHERE clauses
• ✅ JOIN columns
• ✅ ORDER BY columns
• ✅ Frequently queried columns

When NOT to Use Indexes

• ❌ Small tables
• ❌ Columns with low cardinality
• ❌ Columns updated frequently