23 - Indexing
490 words
2 min read
Visual companion
Python
Type and operator map
Python Week 1: the first filter for runtime behavior
View
Revision summary
What this note is really saying
Short form
# 23 - Indexing ## 🎯 Learning Objectives After reading this topic, you will be able to: - Explain why indexing speeds up data access - Distinguish between dense and sparse indices - Distinguish between primary (clustered) and secondary (non-clustered) indices - Compute the number of blocks needed for multi-level in...
23 - Indexing
🎯 Learning Objectives
After reading this topic, you will be able to:
- Explain why indexing speeds up data access
- Distinguish between dense and sparse indices
- Distinguish between primary (clustered) and secondary (non-clustered) indices
- Compute the number of blocks needed for multi-level indices
📖 Core Content
23.1 Intuition: The Library Analogy
A database index is like a book index: it tells you WHERE to find a record without scanning the entire table.
- Without index: Read EVERY block (full table scan) — O(n)
- With index: Navigate the index to find the block — O(log n)
Why This Matters: Indexing is the single most effective performance optimization for databases.
23.2 Index Types
(Diagram)
Dense vs. Sparse Index
| Feature | Dense Index | Sparse Index |
|---|---|---|
| Entries | One per record | One per block |
| Size | Larger | Smaller |
| Speed | Faster (direct to record) | Slightly slower (need block scan) |
| Maintenance | More overhead | Less overhead |
Dense: Every search key value has an index entry pointing to the record. Sparse: Only some search key values have index entries (typically one per block). Requires ordered data.
Primary (Clustered) vs. Secondary (Non-clustered) Index
| Feature | Primary Index | Secondary Index |
|---|---|---|
| Data order | Ordered by search key | Any order |
| Count | One per table | Multiple per table |
| Dense/sparse | Can be sparse | Must be dense |
| Also called | Clustered index | Non-clustered index |
23.3 ISAM (Indexed Sequential Access Method)
Combines sequential file organization with a sparse index:
- Data records are stored sequentially by key
- A sparse index points to blocks
- For inserts: overflow blocks are used
23.4 Multi-Level Index
When the index itself becomes large, we index the index:
Example:
- L1 (innermost): 100,000 blocks
- L2: 100,000 / 50 = 2,000 blocks (index blocking factor = 50)
- L3: 2,000 / 50 = 40 blocks
- L4 (outermost): 40 / 50 = 1 block Block accesses needed: 4 (L4 → L3 → L2 → L1) + 1 (data block) = 5
23.5 Index Design Guidelines
| Consideration | Recommendation |
|---|---|
| Frequent searches | Create index on search key |
| Unique values | Primary key → unique/clustered index |
| Foreign keys | Index foreign key columns for join performance |
| Small tables | Don't index (full scan is faster) |
| Frequent updates | Too many indices slow down INSERT/UPDATE/DELETE |
| Range queries | Clustered (primary) index preferred |
📝 Practice Questions
Q1. What is the difference between dense and sparse indices?
AnswerDense: One index entry per record (larger, faster, more maintenance). Sparse: One index entry per block (smaller, slightly slower, less maintenance).
Q2. A table has 1,000,000 records, block size 4096 bytes, record size 100 bytes, key size 20 bytes, pointer size 6 bytes. How many blocks for a sparse index?
AnswerRecords per block = floor(4096/100) = 40 Data blocks = 1,000,000/40 = 25,000 Index entries per block = floor(4096/26) = floor(157.5) = 157 Index blocks = ceil(25000/157) ≈ 160 blocks
🔗 Cross-References
- Next Topic: 24 - B+ Tree Index
- Previous Topic: 22 - File Organization
- Related: BSCS2002 (PDSA) — Balanced trees
- Textbook: Chapter 11 (Indexing and Hashing) Join Discord Previous22 - File OrganizationNext24 - B+ Tree Index