Below are easy, interview-friendly notes from the uploaded SQL Indexes deep dive. I’ve kept the concepts, key examples, and CTO lenses, but removed unnecessary repetition.
In very simple words: Think of the table like a bookshelf. A clustered index decides how the books themselves are arranged, while a non-clustered index is like a separate card that tells you where a book is. So clustered data is already in the wanted order, while a non-clustered index may require one extra trip to the actual row.
Think of a table as a bookshelf.
CLUSTERED INDEX
= The bookshelf itself
[Book A] [Book B] [Book C] [Book D]
↑
Data is physically
ordered this way
Only ONE physical order is possible.
NON-CLUSTERED INDEX = Card catalog beside the shelf Author Index ──────> Book position Title Index ──────> Book position Date Index ──────> Book position Many indexes can exist.
| Clustered | Non-Clustered |
|---|---|
| Determines physical row order | Separate structure |
| Leaf contains actual row data | Leaf contains key + pointer |
| Only 1 per table | Many possible |
| Usually faster for direct row retrieval | May require extra lookup |
Clustered:
B-Tree → Actual Row
↓
DONE
Non-clustered:
B-Tree → Pointer → Table Row
Because rows can physically be ordered only one way.
Rows: A → B → C → D Cannot simultaneously store: A → B → C → D AND D → B → A → C
Sequential ID
[1][2][3][4][5]
↓
insert 6
[1][2][3][4][5][6]
Usually efficient
Random UUID
[...][...][...][...]
↑
insert
May need PAGE SPLIT
↓
Move existing rows
↓
More I/O + fragmentation
Good clustering key: generally stable + sequential/append-friendly.
A table without clustered index is a heap.
Row → physical location file 2 page 14 slot 3 ↑ RID
If the row physically moves, pointers may need updating.
If most queries are:
WHERE demat_account_id = ?
Prefer:
CLUSTER BY (demat_account_id, holding_id)
instead of only:
holding_id
Because:
By holding_id: h1 h2 h3 h4 h5 ↑ ↑ same customer's holdings scattered By demat_account_id: Account A → h1 h5 h9 Account B → h2 h6 Account C → h3 h4
Remember: Cluster according to the dominant read pattern.
In very simple words: A B-Tree is a smart way of searching a huge table without reading everything. It keeps the tree short, so the database needs only a few expensive disk reads. Because the bottom of the tree is already sorted, it is also very useful for ranges and ORDER BY queries.
The key problem is disk I/O.
Binary Search Tree
[50]
/ \
[25] [75]
...
1M rows → ~20 levels
→ potentially ~20 page reads
B-Tree
[25 | 60]
/ | \
[10|20] [35|47] [75|90]
1M rows → only ~3–4 levels
→ ~3–4 page reads
CPU comparison = CHEAP
Disk page read = EXPENSIVE
B-Tree:
More comparisons
+
Fewer disk reads
↓
Much faster
B-Tree leaves are already sorted.
[10,15] ↕ [20,25] ↕ [30,35] ↕ [40,45]
Query:
ORDER BY created_at
The database can:
Find starting point
↓
Walk through leaf pages
↓
Already sorted
↓
No separate SORT needed
When a page becomes full:
FULL PAGE ↓ SPLIT / \ A B \ / Middle key → Parent
If parent also becomes full:
Child split
↓
Parent split
↓
Root may split
↓
Tree grows upward
All leaves remain at the same depth.
For:
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'
Find Jan 1
↓
[Jan 1] → [Jan 2] → [Jan 3] → ...
↓
Stop at Jan 31
Excellent for range queries.
Jan 1 → Bucket 47 Jan 2 → Bucket 12 Jan 3 → Bucket 81
No ordering.
So:
B-Tree → equality + range + ordering Hash → mainly exact equality
In very simple words: A composite index is like a phone book sorted first by last name, then first name, then middle name. The database gets the biggest benefit when your search starts with the first column(s) of the index. If you skip the first column, the index becomes much less useful for finding the data quickly.
Suppose:
INDEX(user_id, status, created_at)
Think of a phone book:
Last Name ↓ First Name ↓ Middle Name
The database can efficiently start searching from the left.
| Query | Usage |
|---|---|
user_id = 5 | ✅ Full |
user_id = 5 AND status='ACTIVE' | ✅ Full |
user_id=5 AND created_at > ... | ⚠️ Partial |
status='ACTIVE' | ❌ Poor/no seek |
user_id + status + created_at range | ✅ Ideal |
INDEX(A, B, C) A → ✅ A + B → ✅ A + B+C → ✅ A + C → ⚠️ Partial B → ❌ C → ❌
Queries:
1. WHERE status = ? 2. WHERE user_id = ? AND status = ?
Use:
INDEX(user_id, status)
+
INDEX(status)
Because:
(user_id, status)
↑
must start here
The first index cannot efficiently serve:
WHERE status = ?
In very simple words: Having an index does not guarantee that it will be used. If you put a function around a column, start a LIKE search with %, use the wrong data type, match too many rows, or have a tiny table, scanning the table can be cheaper. The optimizer chooses what it thinks is fastest.
Having an index doesn't mean the database will use it.
Bad:
WHERE YEAR(created_at) = 2026
Better:
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
Function on column
↓
Compute for many rows
↓
Index loses usefulness
Bad:
WHERE name LIKE '%esh'
Good:
WHERE name LIKE 'Rag%'
Why?
Index: A... B... C... Rag... S... T...
'Rag%' ↓ Can jump to Rag '%esh' ↓ Could be anywhere
For suffix/contains searches, specialized indexes such as trigram/full-text may be appropriate.
Column:
account_no VARCHAR
Bad:
WHERE account_no = 12345
Better:
WHERE account_no = '12345'
Otherwise the database may need to convert the column, reducing index usability.
Example:
is_active TRUE → 70% FALSE → 30%
Query:
WHERE is_active = TRUE
If 70% of rows match:
Index lookup
↓
70% of table
↓
Many table lookups
A full scan may actually be cheaper.
200 rows ↓ Probably 1–2 pages ↓ Full scan = very cheap
Optimizer may correctly choose:
TABLE SCAN
even though an index exists.
When an indexed query is slow:
1. EXPLAIN ANALYZE
↓
2. Check 5 index traps
↓
3. Check statistics
↓
4. Check leftmost-prefix issue
↓
5. Consider covering / partial / other index
Key principle:
Index exists ≠ Index is useful.
In very simple words: A primary key answers “which row is this?”, while a unique key answers “can two rows have the same value here?”. For example, user_id identifies a user, while PAN can be a business rule that prevents duplicate PAN numbers.
Think:
PRIMARY KEY = IDENTITY UNIQUE KEY = BUSINESS RULE
| Primary Key | Unique Key |
|---|---|
| Identifies the row | Prevents duplicate values |
| NOT NULL | NULL behavior is engine-dependent |
| One per table | Multiple allowed |
| Common FK target | Can also be referenced |
| Often clustering key | Usually separate index |
users user_id → PRIMARY KEY pan_number → UNIQUE
During onboarding:
user_id = 101 pan_number = NULL
Later:
pan_number = ABCDE1234F
This works because PAN is a unique business property, not the row's identity.
In very simple words: Normally the database uses the index to find a row and then goes back to the table to get the remaining columns. A covering index also keeps the needed output columns in the index, so the database can answer the query directly without that extra trip. The trade-off is a larger and more expensive index to maintain.
Normal index:
INDEX(user_id) Query: SELECT status FROM orders WHERE user_id = 5
Flow:
Index ↓ user_id found ↓ Go to table ↓ Get status
Covering index:
INDEX(user_id) INCLUDE(status)
Now:
Index
├── user_id
└── status
↓
Answer directly
↓
No table lookup
This is an index-only scan.
KEY columns
↓
Determine sorting/search
↓
Leftmost prefix applies
INCLUDE columns
↓
Only stored at leaf
↓
Used for retrieval
Rule:
FILTER / SORT → KEY SELECT only → INCLUDE
Query:
SELECT policy_number, premium_amount, status FROM policies WHERE demat_account_id = ? AND status = 'ACTIVE';
Good index:
(demat_account_id, status) INCLUDE (policy_number, premium_amount)
Because:
More columns
↓
Larger index
↓
More storage
↓
More write work
↓
Higher INSERT/UPDATE/DELETE cost
Use covering indexes for important, hot queries.
In very simple words: When a parent row is changed or deleted, the database may need to check many child rows. An index on the foreign-key column lets it find those child rows directly instead of checking the whole child table. This can reduce unnecessary scanning and locking.
Suppose:
demat_accounts
|
| parent
↓
holdings
|
+-- demat_account_id
Delete:
DELETE FROM demat_accounts WHERE id = 100;
Database must check:
"Does holdings contain rows for account 100?"
Delete parent
↓
Scan ALL holdings
↓
Check every row
↓
Locks may be held
↓
Concurrent operations blocked
holdings(demat_account_id)
↓
Direct lookup
↓
Find matching rows quickly
MySQL/InnoDB
→ FK index automatically created
PostgreSQL
→ NOT automatically created
SQL Server
→ NOT automatically created
So for PostgreSQL:
CREATE INDEX idx_holdings_demat_account_id ON holdings(demat_account_id);
Important: FK constraint ≠ FK index.
In very simple words: Different NoSQL databases solve indexing differently because they are built around different data models. MongoDB supports several index types, Cassandra focuses heavily on partition-based access, DynamoDB uses PK/GSI/LSI around access patterns, Elasticsearch indexes documents, and Redis does not provide a general native secondary-index model.
Different databases treat indexes differently.
| Database | Important idea |
|---|---|
| MongoDB | B-Tree, multikey, text, geospatial, TTL, wildcard |
| Cassandra | Partition key determines data placement |
| DynamoDB | Access patterns designed using PK/GSI/LSI |
| Elasticsearch | Documents indexed by default |
| Redis | No native general secondary index |
Document:
{
"tags": ["a", "b", "c"]
}
Index creates:
a → document b → document c → document
But two arrays are problematic:
tags = [a,b,c] colors = [x,y,z] Possible combinations: a-x a-y a-z b-x b-y b-z c-x c-y c-z = 9 combinations
This Cartesian-product explosion is why MongoDB doesn't allow a compound multikey index across two array fields in this way.
In very simple words: In Cassandra, data is spread across nodes using the partition key. Searching by another column can therefore require asking many nodes and combining their answers. That is why the notes recommend designing another table around the query instead of depending heavily on a secondary index.
SQL:
Index ↓ Directly find row
Cassandra:
Data distributed:
Node A
Node B
Node C
Secondary index on non-partition column
↓
Ask ALL nodes
↓
Scatter + Gather
↓
Expensive
This can defeat Cassandra's partitioning model.
Typical solution:
Design another table around the required query
In very simple words: LSI and GSI are two ways to get another view of DynamoDB data. LSI keeps the same partition key but changes the sort key, while GSI can use a completely different partition key. In simple terms, LSI gives another view inside the same group; GSI lets you search using a different grouping.
Same partition key, different sort key.
Base: PK = user_id SK = timestamp LSI: PK = user_id SK = different attribute
Use when:
"Show me different views of ONE user's data"
Different partition key.
Base: PK = user_id GSI: PK = status SK = timestamp
Use when:
"Search/group data using a completely different key"
Same PK + different sort
↓
LSI
New PK / new grouping
↓
GSI
Want:
All FAILED KYC events across all users today
Base:
PK = user_id
LSI ❌ — still user-specific.
GSI ✅
GSI PK = status#date GSI SK = timestamp
Be careful with hot partitions if many records share the same partition key.
In very simple words: SQL is very good when the database itself must enforce rules such as uniqueness and when related data needs to be joined at query time. These operations can be handled atomically and consistently inside the database, whereas distributed NoSQL designs may need denormalization or asynchronous index updates.
SQL:
WRITE ↓ Check UNIQUE index ↓ Write ↓ ONE ATOMIC TRANSACTION
So:
No duplicate gap
DynamoDB GSI:
Base write
↓
Async propagation
↓
GSI updated later
There can be a period where the GSI hasn't caught up.
Therefore: don't use an eventually consistent GSI as your uniqueness guarantee.
Orders | | user_id ↓ Users
SQL can efficiently use indexes to join related data at query time.
In distributed NoSQL:
Node A Orders | | network call ↓ Node B Users
Doing this repeatedly can become expensive.
Therefore NoSQL often prefers:
JOIN at WRITE TIME
↓
Denormalize
↓
Fast READ
SQL:
Table + New Index ↓ New query supported
Cassandra:
Existing partitioning
↓
Cannot efficiently answer
new query
↓
Create another table
↓
New partition strategy
SQL: Model data ↓ Add indexes for queries NoSQL: Know queries first ↓ Model data around queries
In very simple words: A financial ledger needs very strong correctness: money should not be double-counted, transactions should be consistent, relationships should be reliable, and changes should be auditable. The notes therefore show why strong ACID behavior and joins often make SQL a natural fit for this kind of data.
For a ledger:
Debit/Credit
↓
Must be exactly consistent
↓
Must not double-count
↓
Needs relationships
↓
Needs auditing
Strong ACID + joins are valuable.
Don't choose NoSQL simply because:
"Need scale"
Ask:
Do we really need horizontal partitioning here? OR Can SQL + replicas + sharding solve the scale problem?
In very simple words: NoSQL databases can make certain data shapes much simpler. For example, MongoDB can keep an order and its items together in one document, and wildcard indexes can help when fields under a document area change dynamically. The benefit comes from matching the database model to the way the application reads the data.
SQL:
Order
|
+-- order_items table
|
+-- item 1
+-- item 2
+-- item 3
MongoDB:
Order | +-- items: [item1, item2, item3]
Multikey index can index individual array elements.
MongoDB:
Document 1: metadata.riskScore Document 2: metadata.sourceChannel Document 3: metadata.deviceType
Wildcard index:
metadata.$**
↓
Index whatever fields
appear under metadata
Useful when document fields are dynamic.
SQL doesn't work this way because:
SQL schema
↓
Columns defined first
↓
Index references known columns
In very simple words: With strong consistency, the system waits for the required updates before saying “success”, so reads are more reliably fresh but can take longer. With eventual consistency, the system responds sooner and lets replicas or indexes catch up later. That trade-off is useful for non-critical views but risky for money, balances, and uniqueness.
Write ↓ Update index/replicas ↓ Confirm ↓ Return success
More consistency, potentially more coordination/latency.
Write ↓ Return quickly ↓ Index catches up
Faster, but a read immediately afterward may temporarily see stale/missing data.
Good for:
Search Recent activity feed Non-critical views
Bad for:
Money Balance Uniqueness Critical financial state
In very simple words: A TTL index lets MongoDB automatically remove documents after their expiry time. Instead of writing a separate cleanup job that repeatedly finds and deletes old records, the database performs the background cleanup. This is useful for temporary data such as logs, sessions, and webhook payloads.
Traditional SQL cleanup:
Cron ↓ DELETE old rows ↓ Monitor job ↓ Handle failures
MongoDB TTL:
TTL Index
↓
Database background process
↓
Expired documents removed
Useful for:
Logs Temporary data Sessions Webhook payloads
In very simple words: The example receives many webhook messages that can have different JSON shapes and only need to be kept for a limited period. MongoDB fits because it handles flexible documents, can search by insurer and time, can index changing metadata, and can automatically expire old records with TTL.
Requirement:
Raw webhook payload
↓
High volume
↓
Different JSON shapes
↓
Keep for 90 days
Good fit:
MongoDB
|
+-- compound index
| (insurer_id, received_at)
|
+-- TTL index
| received_at → 90 days
|
+-- wildcard index
dynamic metadata
Architecture:
PolicyBazaar
|
↓
Webhook
|
↓
MongoDB
|
+---- Query by insurer/time
|
+---- TTL auto-removes after 90 days
|
+---- Supports flexible JSON
In very simple words: The big idea is that SQL usually separates the data model from the indexes you add for queries, while NoSQL often designs data placement around the queries from the beginning. Every index should be treated as a deliberate bet on an important query, because it can improve reads but also add storage and write cost.
The most important idea across all 10 topics:
DATABASE DESIGN
|
+---------+---------+
| |
SQL NoSQL
| |
Data + indexes Data placement
are separate = access pattern
| |
Add index later Design query first
| |
Strong ACID Scale/flexibility
Every index is a BET on a QUERY PATTERN.
Before creating an index, ask:
"What query am I optimizing?"
↓
How often is it executed?
↓
How selective is it?
↓
What are the write costs?
↓
Is this the right data model?
CTO-level takeaway:
Don't ask "SQL or NoSQL?" globally. Ask "What does this particular data need — consistency, query flexibility, scale, schema flexibility, or speed?" Then choose the storage and indexing strategy accordingly.