SQL Single-Column Index
The simplest, most common shape — one column, indexed on its own.
What & Why
A single-column index covers exactly one column. It's the right choice whenever queries filter or sort on that column alone, without needing other columns alongside it.
See How It Works
BUSINESS QUESTION
Marketing wants a single-column lead-source index proposal and the distribution used to evaluate it.
| id | campaign_id | created_at | qualified_at | converted_at | lead_score | source | country | |
|---|---|---|---|---|---|---|---|---|
| 301 | 1 | ana@example.com | 2024-01-21 09:10:00+00 | 2024-01-22 11:00:00+00 | 2024-02-02 10:00:00+00 | 86 | google_ads | US |
| 302 | 1 | ben@example.com | 2024-01-24 12:40:00+00 | NULL | NULL | 52 | google_ads | CA |
| 303 | 2 | chloe@example.com | 2024-02-16 08:30:00+00 | 2024-02-18 14:20:00+00 | NULL | 74 | GB | |
| 304 | 3 | dev@example.com | 2024-03-20 17:15:00+00 | 2024-03-21 09:00:00+00 | 2024-04-04 16:00:00+00 | 91 | US |
EXAMPLE QUERY
-- Proposed DDL: CREATE INDEX idx_marketing_leads_source ON marketing.leads (source);
WITH distribution AS (
SELECT source, COUNT(*) AS matching_rows
FROM marketing.leads
GROUP BY source
)
SELECT
source,
matching_rows,
ROUND(100.0 * matching_rows / NULLIF(SUM(matching_rows) OVER (), 0), 2) AS row_percentage
FROM distribution
ORDER BY matching_rows DESC, source;RESULT — lead-source distribution
| source | matching_rows | row_percentage |
|---|---|---|
| google_ads | 2 | 50.00 |
| 1 | 25.00 | |
| 1 | 25.00 |
The distribution is the real Queryflo input for a single-column index decision.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario