Understanding Database Indexes: The Key to Performance

Imagine you're looking for a specific topic in a 1,000-page textbook.

• Without an index: You have to flip through every single page until you find it. (Full Table Scan)
• With an index: You go to the back of the book, find the topic alphabetically, and jump straight to the page number. (Index Lookup)

This is exactly how database indexes work. They are special data structures (usually B-Trees) that help the database find rows without scanning the entire table.

Creating an Index

The syntax is simple:

CREATE INDEX index_name ON table_name (column_name);

Let's see it in action. We'll use EXPLAIN QUERY PLAN to see how the database executes our query.

Before Indexing: Full Table Scan

Result: You'll likely see SCAN TABLE users or similar. This means the database looked at every row.

After Indexing: Search Table using Index

Now let's add an index on the email column.

Result: You should see SEARCH TABLE users USING INDEX idx_users_email. This is much faster!

Composite Indexes (Multi-column)

You can index multiple columns at once. This is useful when you often query by multiple fields together.

CREATE INDEX idx_name_dept ON employees (department, salary);

Important Rule: The order matters! This index helps queries like:

• WHERE department = 'Sales'
• WHERE department = 'Sales' AND salary > 50000

But it WON'T help:

• WHERE salary > 50000 (because salary is the second part of the index, like trying to find someone in a phone book by their first name only).

The Cost of Indexing

If indexes make reads faster, why not index every column?

Because indexes slow down writes.

Every time you INSERT, UPDATE, or DELETE a row, the database has to update the table AND all the indexes.

• Read-heavy apps: More indexes are fine.
• Write-heavy apps: Be careful with too many indexes.

When to Create an Index

✅ Do Index:

• Primary Keys (usually indexed automatically)
• Foreign Keys (for JOIN performance)
• Columns frequently used in WHERE, ORDER BY, and GROUP BY clauses
• Columns with high cardinality (many unique values, like email or UUID)

❌ Don't Index:

• Small tables (scanning 10 rows is faster than loading an index)
• Columns with low cardinality (e.g., gender or is_active boolean - the index won't narrow down the search enough to be worth it)
• Columns that are frequently updated but rarely queried

Key Takeaways

1. Indexes speed up reads but slow down writes.
2. Use EXPLAIN QUERY PLAN to verify if your index is being used.
3. Composite indexes are powerful, but column order is critical (Leftmost Prefix Rule).
4. Don't over-index; focus on the columns you actually query.

Mastering indexes is the single most effective way to improve database performance. A well-placed index can turn a 10-second query into a 10-millisecond one!