Inkdown
Start writing

Study

70 files·12 subfolders

Shared Workspace

Study
AI eng

04-indexing

Shared from "Study" on Inkdown

04 - Indexing Deep Dive

What is an Index?

An index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional storage and slower writes.

Analogy: Book Index vs Database Index
Plain text
basic-ques
core
Revision w/ Whiteboard
CN Basics - 1
CN Basics - 2
DNS
Event loop
programming-language-concepts.md
zero-language-explanation.md
DB
Quick
databases-deep-dive.md
01-introduction.md
02-relational-databases.md
03-database-design.md
04-indexing.md
05-transactions-acid.md
06-nosql-databases.md
07-query-optimization.md
08-replication-ha.md
09-sharding-partitioning.md
10-caching-strategies.md
11-cap-theorem.md
12-connection-pooling.md
13-backup-recovery.md
14-monitoring.md
15-database-selection.md
README.md
JS
core topics
Event loop
Merlin Backend
01-Orchestration.md
02-DeepResearch.md
03-Search.md
04-Scraping.md
05-Streaming.md
06-MultiProviderLLM.md
07-MemoryAndContext.md
08-ErrorHandling.md
09-RateLimiting.md
10-TaskQueue.md
11-SecurityAndAuth.md
Orchestration-2nd-draft
Mobile
Build Alternative
Bundling
metro-bundler-deep-dive.md
OpenAI Agents Python
00_OVERVIEW.md
01_AGENT_SYSTEM.md
02_RUNNER_SYSTEM.md
03_TOOL_SYSTEM.md
04_ITEMS_SYSTEM.md
05_GUARDRAILS.md
06_HANDOFFS.md
07_MEMORY_SESSIONS.md
08_MODEL_PROVIDERS.md
09_SANDBOX_SYSTEM.md
10_TRACING.md
11_RUN_STATE.md
12_CONTEXT.md
13_LIFECYCLE_HOOKS.md
14_CONFIGURATION.md
15_ERROR_HANDLING.md
16_STREAMING.md
17_EXTENSIONS.md
18_MCP_INTEGRATION.md
19_BEST_PRACTICES.md
20_ARCHITECTURE_PATTERNS.md
opencode-study
context-handling
core
Python
Alembic
Basics
sqlalchemy - fastapi
SQLAlchemy overview
tweets
system_design_for_agentic_apps.md
Agent Loop

Why Indexes Matter

Sql

How Indexes Work: B-Trees

Most database indexes use B-Tree (Balanced Tree) structure:

Plain text
B-Tree Node Structure
Plain text

Types of Indexes

1. Single-Column Index
Sql
2. Composite (Multi-Column) Index
Sql
3. Unique Index
Sql
4. Partial Index
Sql
5. Expression Index
Sql
6. Covering Index (Index Only Scan)
Sql
7. Full-Text Index
Sql
8. GiST / GIN / SP-GiST / BRIN (PostgreSQL Special)
Sql

Index Selectivity

Selectivity = (Number of unique values) / (Total rows)

Higher selectivity = better index performance

Plain text

Index Strategies by Query Type

Equality Queries
Sql
Range Queries
Sql
Pattern Matching
Sql
Sorting & Pagination
Sql
Joins
Sql

How to Create Good Indexes

Step-by-Step Process
Sql
EXPLAIN Output Decoded
Plain text

Index Maintenance

1. Fragmentation
Sql
2. Unused Indexes
Sql
3. Duplicate/Redundant Indexes
Sql

Index Trade-offs

Storage Cost
Sql
Write Performance
Plain text
Locking
Sql

Special Indexing Scenarios

1. Indexing for JSON/JSONB
Sql
2. Indexing Arrays
Sql
3. Partial Index for Soft Deletes
Sql
4. Index for Ordering
Sql

Index Checklist

  • Primary keys always indexed (automatic)
  • Foreign keys indexed
  • Columns in WHERE clauses indexed
  • ORDER BY columns indexed
  • JOIN columns indexed
  • High-selectivity columns preferred
  • Composite indexes ordered correctly (equality first)
  • Covering indexes for common queries
  • Partial indexes for filtered queries
  • Expression indexes for computed WHERE
  • Regular review of unused indexes
  • Index size monitored

Common Mistakes

  1. Indexing low-cardinality columns (boolean, status with few values)
  2. Too many single-column indexes instead of composite
  3. Wrong column order in composite indexes
  4. Not analyzing (updating statistics) after bulk loads
  5. Indexing everything (wastes space, slows writes)
  6. Forgetting to index foreign keys
  7. Using indexes for small tables (sequential scan is faster)

Next: Transactions & ACID