Course contentsShow
← System Design
Lesson 1521 of 1,91937. Real-World System: URL ShortenerFree lesson

Indexing Strategy for Fast Lookups

Create indexes on short_code for O(1) redirects and on user_id for listing user URLs, balancing read and write performance.

Indexing Strategy for Fast Lookups

What you'll learn: How to design indexes that make URL lookups instant while keeping writes fast enough for high-throughput shortener systems.

The Core Challenge

In a URL shortener, you have two critical access patterns: redirecting users (looking up the original URL by short code) and displaying a user's links (finding all URLs created by a specific user). Without proper indexes, the database would scan entire tables—turning millisecond operations into second-long disasters at scale.

Primary Index: Short Code

Your most critical index is on short_code. This should be:

  • Unique (enforced at database level)
  • Clustered or covering (stores URL directly in index)
  • Delivers O(1) lookup time for redirects

Since 95%+ of operations are reads (redirects), this index lives entirely in memory on most systems. Think of it like a phone's speed dial—instant access to frequently needed numbers.

Secondary Index: User ID

For the "list my URLs" feature, create an index on user_id:

  • Allows fast filtering: WHERE user_id = ?
  • Typically includes created_at for sorting
  • Composite index: (user_id, created_at) covers common queries

This trades some write performance (every insert updates two indexes) for fast user dashboard loads.

The Tradeoff

Each index speeds reads but slows writes slightly. With a read-heavy system (99:1 read-to-write ratio), this is worth it. However, avoid over-indexing—indexing original_url rarely helps since you don't search by destination.

Key Takeaway: Index short_code for O(1) redirects and user_id for dashboard queries; in read-heavy systems like URL shorteners, the write overhead is negligible compared to the massive read speedup.