Lesson 52 of 61

5 mins read

SQL CREATE INDEX: B-Trees & Query Optimization

In Plain English

Without an index, the database engine must perform a Sequential Scan (reading every row on disk) to evaluate `WHERE` conditions. `CREATE INDEX` builds an auxiliary B-Tree search structure mapping column values to row storage locations (RowIDs/Pointers), turning slow linear scans into logarithmic seek operations. Proper indexing is the single most impactful performance lever in relational databases.

The Mental Model

"An index is the alphabetized index at the back of a 1,000-page textbook: instead of reading every page to find "Photosynthesis", you look it up in the index and jump directly to page 432."

Deep Dive: How It Works

B-Tree Traversal: Navigates balanced trees in $O(\log N)$ time to pinpoint exact row storage addresses.

Composite Indexes & Left-to-Right Rule: An index on `(last_name, first_name)` accelerates queries on `last_name` OR `(last_name, first_name)`, but NOT on `first_name` alone.

Index Overhead: Every index speeds up `SELECT` queries but slightly slows down `INSERT`, `UPDATE`, and `DELETE` operations because the index B-tree must be rebalanced.

Syntax Blueprint

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
CREATE UNIQUE INDEX idx_unq_slug ON articles(slug);

Builds single-column, composite, or unique B-Tree search indices.

Core Rules to Remember

$O(\log N)$ Lookups: Reduces lookup time on billions of records from seconds to milliseconds.

Composite Column Order: Place the most selective columns and equality filters on the left side of composite indices.

Write Overhead Tradeoff: Index high-frequency query paths; avoid over-indexing rarely queried columns.

Common Beginner Traps & How to Fix Them

Mistake:Applying a function to an indexed column in the WHERE clause (e.g. `WHERE LOWER(email) = "[email protected]"`).

Why it happens: Wrapping the column in a function prevents the engine from using the standard B-Tree index, forcing a full table scan.

How to fix: Create an Expression/Functional Index (e.g. `CREATE INDEX idx_email ON users(LOWER(email))`) or query exact matches.

Pro Tip: Use `EXPLAIN QUERY PLAN SELECT ...` in SQLite or `EXPLAIN ANALYZE` in PostgreSQL to verify whether your query uses an `INDEX SCAN` or falls back to a `SCAN TABLE`.

Live Interactive Example

Hit Run Code to see it live

Creating Composite Indexes for Search Acceleration

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: Transactions filtered directly using the composite B-Tree index.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Create and Query Index

Create an index `idx_tx_amount` on `transactions(amount)`.
Query all transactions where `amount > 100.0`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.