SQL Indexes: How to Create, Manage & Optimize Performance

In This Chapter

Understanding Indexes
Creating and Managing Indexes
Impact of Indexes on Performance

1 Understanding Indexes

An index is a data structure that speeds up data retrieval. Think of it like the index at the back of a textbook — instead of reading every page to find "INNER JOIN", you look it up in the index and go directly to page 47. Without an index, the database scans every row in the table (full table scan) to find matches.

Without vs With an Index

SQL

-- This query on a 10-million-row table:
SELECT * FROM users WHERE email = 'sara@email.com';

-- Without index: scans all 10,000,000 rows       → 3.2 seconds
-- With index on email: finds it in ~3 lookups     → 0.001 seconds

That's a 3,200x speedup from a single index.

How Indexes Work: B-Tree

Most SQL indexes use a B-Tree (balanced tree) structure. The data is sorted and organized in a tree, so finding a value takes O(log n) operations instead of O(n):

Table Rows	Full Scan (no index)	B-Tree Lookup (with index)
1,000	1,000 comparisons	~10 comparisons
100,000	100,000 comparisons	~17 comparisons
10,000,000	10,000,000 comparisons	~23 comparisons
1,000,000,000	1 billion comparisons	~30 comparisons

What Gets Indexed Automatically?

Constraint	Auto-Index?	Why
`PRIMARY KEY`	Yes (always)	Must be unique and fast to look up
`UNIQUE`	Yes	Database needs to check for duplicates on every INSERT
`FOREIGN KEY`	Depends on DBMS	MySQL: yes (InnoDB). PostgreSQL: no (add manually!)
Regular columns	No	You must create these indexes yourself

Analogy: A table without indexes is like a phone book sorted randomly. To find "Sara Ahmed", you'd read every entry. An index is like sorting the phone book alphabetically — you can jump to the "S" section and find Sara in seconds.

Key Takeaways

An index is a sorted data structure that speeds up SELECT queries dramatically
B-Tree indexes turn O(n) scans into O(log n) lookups
PRIMARY KEY and UNIQUE columns are indexed automatically
For other columns you search/filter on frequently, you must create indexes manually

2 Creating and Managing Indexes

Top

Create a Basic Index

SQL

-- Index on the email column for fast lookups
CREATE INDEX idx_users_email
ON users (email);

Create a Unique Index

SQL

-- Enforces uniqueness AND speeds up lookups
CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);

Composite Index (Multi-Column)

When queries filter on multiple columns together:

SQL

-- Queries that filter by both country AND city
CREATE INDEX idx_users_country_city
ON users (country, city);

-- This query uses the index:
SELECT * FROM users WHERE country = 'UAE' AND city = 'Dubai';

-- This also uses it (leftmost prefix):
SELECT * FROM users WHERE country = 'UAE';

-- This does NOT use it (skips the first column):
SELECT * FROM users WHERE city = 'Dubai';  -- ❌

Left-prefix rule: A composite index on (country, city) can be used for queries on country alone, or country + city, but not for city alone. Column order matters! Put the most frequently filtered column first.

View Existing Indexes

SQL

-- MySQL
SHOW INDEX FROM users;

-- PostgreSQL
SELECT indexname, indexdef FROM pg_indexes
WHERE tablename = 'users';

Drop an Index

SQL

-- MySQL
DROP INDEX idx_users_email ON users;

-- PostgreSQL / SQL Server
DROP INDEX idx_users_email;

Index Types at a Glance

Type	Best For	Example
B-Tree (default)	Equality and range queries (`=`, `<`, `>`, `BETWEEN`)	Most use cases
Hash	Exact equality only (`=`)	Session tokens, lookup tables
Full-Text	Text search (`MATCH ... AGAINST`)	Blog content, product descriptions
Spatial	Geographic data	Location-based queries

Key Takeaways

CREATE INDEX name ON table (column) adds an index
Composite indexes cover multiple columns — order matters (left-prefix rule)
SHOW INDEX FROM table (MySQL) to see existing indexes
B-Tree is the default and covers 95% of use cases
Drop unused indexes — they waste space and slow down writes

3 Impact of Indexes on Performance

Top

Indexes are not free. They speed up reads but slow down writes. Understanding this trade-off is critical for database design.

The Trade-Off

Operation	Without Index	With Index
SELECT with WHERE	Slow (full table scan)	Fast (index lookup)
SELECT with ORDER BY	Slow (sort in memory)	Fast (already sorted)
JOIN on column	Very slow on large tables	Fast (index on join column)
INSERT	Fast (just append)	Slower (must update index too)
UPDATE indexed column	Fast	Slower (must update index)
DELETE	Fast	Slower (must update index)
Disk space	Table only	Table + index data

How to Verify: EXPLAIN

The EXPLAIN command shows you how the database plans to execute your query — whether it uses an index or does a full scan:

SQLEXPLAIN SELECT * FROM users WHERE email = 'sara@email.com';

Without index

type: ALL          ← Full table scan! BAD for large tables
rows: 10000000     ← Scanning all 10M rows
Extra: Using where

With index on email

type: ref          ← Index lookup! GOOD
key: idx_users_email
rows: 1            ← Only reading 1 row
Extra: NULL

When to Add an Index

Columns in WHERE clauses that you filter on frequently
Columns in JOIN ON clastrong> conditions (especially foreign keys)
Columns in ORDER BY to avoid in-memory sorting
Columns in GROUP BY to speed up aggregation
Columns with high cardinality (many unique values — email, UUID) work best

When NOT to Add an Index

Small tables (under ~1000 rows) — full scan is already fast
Columns rarely used in WHERE/JOIN — the index sits unused but slows writes
Low cardinality columns (e.g., gender with only 2 values) — index doesn't help much
Tables with heavy INSERT/UPDATE loads — too many indexes slow down writes
Columns you never filter on — don't index what you don't query

Rule of thumb: Start with indexes on PRIMARY KEY (automatic), FOREIGN KEY columns, and any column that appears in WHERE clauses of your most frequent queries. Monitor slow queries, run EXPLAIN, and add indexes where you see full table scans. Don't over-index — each index costs write performance and storage.

Real-World Indexing Checklist

Check	Action
EXPLAIN shows `type: ALL`	Add an index on the filtered column
JOIN is slow on large tables	Index both sides of the ON condition
ORDER BY causes "Using filesort"	Add an index on the sort column
INSERT/UPDATE is slow	Check for too many indexes — remove unused ones
Table has 50+ indexes	Audit and remove duplicates/unused indexes

Key Takeaways

Indexes speed up SELECT but slow down INSERT/UPDATE/DELETE
Use EXPLAIN to check if your queries use indexes or do full scans
Index columns used in WHERE, JOIN ON, ORDER BY, and GROUP BY
Don't index small tables, low-cardinality columns, or columns you never filter on
Monitor, measure, then index — don't guess

Congratulations! You've completed the SQL Intermediate Course. You now understand views, stored procedures, triggers, normalization, transactions, error handling, advanced queries (subqueries, CTEs, set operators), and indexes. Ready for the final level? The SQL Advanced Course covers window functions, performance tuning, security, locks, deadlocks, and strategies for working with large databases.

All Chapters in SQL Intermediate Course

Continue to Advanced

Previous ChapterAdvanced SQL Queries Next Course SQL Advanced Course

SQL Indexes

In This Chapter

1 Understanding Indexes

Without vs With an Index

How Indexes Work: B-Tree

What Gets Indexed Automatically?

Key Takeaways

2 Creating and Managing Indexes

Create a Basic Index

Create a Unique Index

Composite Index (Multi-Column)

View Existing Indexes

Drop an Index

Index Types at a Glance

Key Takeaways

3 Impact of Indexes on Performance

The Trade-Off

How to Verify: EXPLAIN

When to Add an Index

When NOT to Add an Index

Real-World Indexing Checklist

Key Takeaways

All Chapters in SQL Intermediate Course

Continue to Advanced

Courses

Services

Support

Connect