About Database Indexes

Database indexes are data structures that improve the speed of data retrieval operations. Like an index in a book, they allow the database to find data without scanning every row.

When to Use Indexes

• WHERE Clauses: Columns frequently used in filtering conditions
• JOIN Conditions: Columns used to join tables together
• ORDER BY: Columns used for sorting results
• Foreign Keys: Columns that reference other tables
• High Cardinality: Columns with many unique values

Types of Indexes

Single Column Index

Index on a single column, useful for simple queries.

CREATE INDEX idx_users_email ON users(email);

Composite Index

Index on multiple columns, useful for queries that filter on multiple columns.

CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

Unique Index

Ensures all values in the indexed column are unique.

CREATE UNIQUE INDEX idx_users_username ON users(username);

Full-Text Index

Optimized for text search queries.

CREATE FULLTEXT INDEX idx_posts_content ON posts(title, content);

Index Performance Impact

Operation	Without Index	With Index
SELECT with WHERE	Slow (Full table scan)	Fast (Index lookup)
JOIN operations	Slow (Nested loops)	Fast (Index joins)
ORDER BY	Slow (Sort required)	Fast (Pre-sorted)
INSERT/UPDATE/DELETE	Fast	Slower (Index maintenance)

Best Practices

• Start Small: Add indexes based on actual query patterns
• Monitor Usage: Use EXPLAIN to verify index usage
• Composite Order: Put most selective columns first
• Avoid Over-Indexing: Too many indexes slow down writes
• Regular Maintenance: Rebuild fragmented indexes
• Cover Queries: Create covering indexes when possible

Query Analysis Example

-- Check if index is being used
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- Results show:
-- type: ref (using index)
-- key: idx_users_email
-- rows: 1 (instead of 100,000)

Common Index Pitfalls

• Indexing low-cardinality columns (e.g., boolean fields)
• Not considering index column order in composite indexes
• Creating redundant indexes
• Ignoring index maintenance (fragmentation)
• Using functions on indexed columns (prevents index usage)