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_atfor 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.