Course contentsShow
← Software Engineering
Lesson 871 of 1,93327. Database Design in PracticeFree lesson

Identifying Columns to Index

Choosing columns based on query patterns: WHERE clauses, JOIN conditions, and ORDER BY operations.

Identifying Columns to Index

What you'll learn: How to choose which database columns deserve indexes by analyzing your application's query patterns.

The Query-Driven Approach

Not every column needs an index. The key is to let your queries tell you what to index. Think of indexes like bookmarks in a textbook—you only add them for pages you'll visit frequently, not every single page.

Three Query Patterns to Watch

WHERE Clauses
Any column frequently used in WHERE conditions is a prime indexing candidate. If you're constantly filtering by user_id or email, those columns should be indexed. Without an index, the database must scan every row—like reading every page to find one reference.

-- This query screams for an index on email
SELECT * FROM users WHERE email = 'alice@example.com';

JOIN Conditions
Columns used in JOIN operations benefit hugely from indexes. Both the foreign key column and the referenced primary key should be indexed (primary keys usually are by default).

-- Index orders.user_id and users.id
SELECT * FROM orders 
JOIN users ON orders.user_id = users.id;

ORDER BY Operations
Sorting without an index forces the database to retrieve all matching rows, then sort them in memory. An index on the sorted column keeps data pre-sorted.

-- Index created_at for fast chronological queries
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;

Start with Measurement, Not Guesses

Review your actual query logs and slow query reports. Don't index speculatively—index based on real usage patterns.

Key Takeaway: Index columns that appear frequently in WHERE clauses, JOIN conditions, and ORDER BY operations—let your application's actual query patterns guide your indexing decisions.