✏️ Explanatory Question
Level: Hard — A classic system-design-meets-SQL question; tests schema, indexing, ID generation, and scale awareness.
bit.ly/aB3x9Z); redirect the short code back to the original; track click analytics; and it must scale to billions of URLs. Walk through your tables, keys, and the code-generation strategy.
bit.ly/my-brand).Using base62 (a–z, A–Z, 0–9) with a 7-character code:
$$ 62^7 = 3{,}521{,}614{,}606{,}208 \approx 3.5 \text{ trillion URLs} $$
-- Main table: maps short code <-> long URL
CREATE TABLE urls (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- internal sequential id
short_code VARCHAR(10) NOT NULL, -- base62 code
long_url VARCHAR(2048) NOT NULL,
user_id BIGINT, -- owner (nullable = anon)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expires_at TIMESTAMP NULL, -- optional expiry
is_active BOOLEAN DEFAULT TRUE,
UNIQUE KEY uq_short_code (short_code) -- fast lookup + no dupes
) ENGINE=InnoDB;
-- Analytics: one row per click (write-heavy, can be partitioned by date)
CREATE TABLE clicks (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
url_id BIGINT NOT NULL,
clicked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ip_address VARBINARY(16), -- IPv4/IPv6 packed
country CHAR(2),
referrer VARCHAR(512),
user_agent VARCHAR(512),
INDEX idx_url_time (url_id, clicked_at)
) ENGINE=InnoDB;UNIQUE index on short_code is everything — the redirect path does SELECT long_url FROM urls WHERE short_code = ? billions of times. It must be a single, O(log n) unique-index lookup.
| Strategy | How | Trade-off |
|---|---|---|
| Encode auto-id (best) | base62-encode the sequential id |
No collisions ever; but codes are guessable/sequential |
| Random generation | Random 7 base62 chars, check UNIQUE | Unguessable; needs collision retry |
| Hash (MD5/SHA) | Hash URL, take first 7 chars | Collisions possible; same URL → same code |
-- 1) Insert the URL, let AUTO_INCREMENT assign a unique id
INSERT INTO urls (short_code, long_url) VALUES ('', 'https://example.com/very/long/path');
SET @new_id = LAST_INSERT_ID();
-- 2) base62-encode @new_id in the app -> e.g., id 125 -> 'cb'
-- Then store it back:
UPDATE urls SET short_code = base62_encode(@new_id) WHERE id = @new_id;
-- REDIRECT PATH (the hot query, billions/day):
SELECT long_url FROM urls
WHERE short_code = 'aB3x9Z' AND is_active = TRUE
AND (expires_at IS NULL OR expires_at > NOW());urls table by a hash of short_code.clicks by date — drop old partitions cheaply; keep analytics off the hot path.urls.click_count = click_count + 1 on every redirect creates a write hotspot and lock contention on popular URLs. Instead, insert into the append-only clicks table (or a queue) and aggregate asynchronously.