Index Selectivity & Cardinality
debt(d5/e3/b5/t7)
Closest to 'specialist tool catches it' (d5), because the detection_hints.tools list includes mysql-explain, pt-query-digest, and mysql-workbench — all specialist database tools. The code_pattern confirms the signal: EXPLAIN output showing many rows examined despite an index existing is only visible when you run EXPLAIN explicitly, not through a default linter or compiler. The problem is silent in normal development and requires deliberate profiling or query analysis.
Closest to 'simple parameterised fix' (e3), grounded in the quick_fix: reordering composite index column order or replacing a low-cardinality leading column with a high-cardinality one (e.g. swapping boolean first-column for user_id). This is typically a targeted schema change — DROP INDEX then CREATE INDEX with corrected column order — affecting one table/component. It's more than a one-line patch because it may require a migration and validation, but it doesn't span the codebase.
Closest to 'persistent productivity tax' (b5), because index decisions apply to both web and cli contexts per applies_to, and a poorly selective index silently degrades query performance across any code path that touches the indexed table. Every future query added against that table is shaped by the existing index strategy, and the tags (performance, database, sql, indexes) confirm this is a cross-cutting database-layer concern. It doesn't define the entire system's shape, but it persistently taxes many work streams that hit the affected table.
Closest to 'serious trap — contradicts how a similar concept works elsewhere' (t7), grounded in the misconception: developers familiar with indexing in general believe 'any column in a WHERE clause should have an index,' which is a reasonable and widely-taught rule of thumb. The contradiction is that adding an index on a low-cardinality column (the natural thing to do) can actively harm performance because the optimizer skips it or incurs double I/O. The common_mistakes reinforce this: assuming an index exists means it will be used directly contradicts the intuitive mental model, and composite index column ordering being counterintuitive adds another layer of surprise.
Also Known As
TL;DR
Explanation
Index selectivity is the ratio of distinct values to total rows. A unique column (email) has selectivity 1.0 — every lookup eliminates all but one row, maximising index efficiency. A boolean column (is_active) has selectivity ~0.5 — the index often leads to a full scan anyway because the planner estimates retrieving 50% of rows is cheaper than using the index. Cardinality is the count of distinct values — CHECK cardinality with SELECT COUNT(DISTINCT col) / COUNT(*). Composite index column order matters: put the highest-cardinality, most-frequently-filtered column first. For low-cardinality columns like status, use partial indexes (WHERE status = 'pending') in PostgreSQL to index only the relevant subset, dramatically reducing index size and improving selectivity.
Common Misconception
Why It Matters
Common Mistakes
- Indexing a boolean column expecting it to speed up queries — the optimizer often ignores it.
- Not checking cardinality with SHOW INDEX or pg_stats before adding an index.
- Composite index column order not matching query selectivity — put the most selective column first.
- Assuming an index exists means it will be used — the query planner makes that decision based on selectivity and row counts.
Code Examples
-- Low selectivity index — optimizer likely ignores it:
CREATE INDEX idx_users_active ON users (is_active); -- Only 2 values: 0 or 1
-- For 1M users, 900K are active — index not selective enough to be useful
-- High selectivity — optimizer uses it:
CREATE INDEX idx_users_email ON users (email); -- Near-unique per row-- Check cardinality before adding an index
SELECT
COUNT(DISTINCT status) AS unique_values,
COUNT(*) AS total_rows,
ROUND(COUNT(DISTINCT status) / COUNT(*) * 100, 2) AS selectivity_pct
FROM orders;
-- status has 4 values / 1M rows = 0.0004% selectivity → poor index candidate alone
-- user_id has 100k values / 1M rows = 10% → decent; combine with status for composite
-- Partial index for low-cardinality column (PostgreSQL)
-- Only index the minority case that benefits from fast lookup
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending'; -- 2% of rows — now highly selective