Cardinality

A word this course has already used three different ways — here's the fourth

The word "cardinality" has shown up in this course before, meaning something different each time, and it is essential to keep them straight:

• Chapter 4 (relational model) Cardinality: the number of rows (tuples) in a relation — e.g., "the students relation has a cardinality of 1,000,000" simply means it has 1,000,000 rows.
• Chapter 6 (ER modeling) Cardinality Ratio: describes the shape of a relationship between two entities — 1:1, 1:N, or M:N (deliberately slugged cardinality-ratio there specifically to avoid collision with this different meaning).
• Chapter 16 (joins) Join Cardinality: an estimate of how many rows a join between two tables will produce.
• Chapter 20.13 — Index Cardinality (this topic): the number of distinct values present in a particular column, considered specifically in relation to indexing decisions.

These are genuinely different concepts that happen to share the English word "cardinality" because they all relate to "counting something." Index cardinality is about counting distinct values within one column — not row totals, not relationship shape, not join output size.

What index cardinality actually is

For a column in a table, index cardinality = the number of unique/distinct values that column contains. In our 1,000,000-row students table:

• email has index cardinality ≈ 1,000,000 (nearly every value is distinct).
• department has index cardinality ≈ 20 (only 20 distinct department names exist, no matter how many rows there are).
• is_active has index cardinality = 2 (true/false, regardless of row count).

The direct link to Selectivity (20.12)

Index cardinality and selectivity are two sides of the same coin:

selectivity = index cardinality / total rows

email's high index cardinality (≈1,000,000) relative to the table's 1,000,000 rows gives it a selectivity near 1.0. is_active's low index cardinality (2) relative to 1,000,000 rows gives it a selectivity near 0. Cardinality is the raw count; selectivity is that count normalized against the table size, expressed as a ratio the optimizer can compare consistently across differently-sized tables.

Why the optimizer cares

The query optimizer maintains statistics — including a column's cardinality — specifically to estimate, for a given query, how many rows a filter on that column is likely to return, and therefore whether using an index (versus a full scan) is worth the cost. A column with high cardinality relative to its table size is very likely to be worth indexing; extremely low cardinality (like is_active) usually is not, echoing 20.12's conclusion from the opposite angle.

Edge cases

• Cardinality is a raw count (independent of table size), while selectivity is a ratio (cardinality relative to table size) — a column with cardinality 1,000 could be highly selective on a 1,000-row table (selectivity = 1.0) but not selective at all on a 1,000,000,000-row table (selectivity = 0.000001). Always consider them together, not in isolation.
• Database engines periodically recompute/refresh these statistics (e.g., via ANALYZE commands), because cardinality can drift as data changes — stale statistics can lead the optimizer to make poor indexing decisions.
• Do not confuse this topic's "index cardinality" (distinct values in a column) with Chapter 4's relational cardinality (total row count of a relation) — despite the shared word, they measure entirely different things, which is exactly why this topic uses the slug index-cardinality rather than the bare cardinality already used elsewhere in this course.

Interview-style Q&A

Q: How does index cardinality differ from the relational-model cardinality covered in Chapter 4? A: Chapter 4's cardinality is the total row count of a relation; index cardinality is the count of distinct values within one specific column — a completely different measurement despite sharing the word.

Q: How do index cardinality and selectivity relate mathematically? A: Selectivity = index cardinality ÷ total rows — cardinality is the raw distinct-value count, selectivity normalizes it into a ratio the optimizer can use consistently.

Key takeaway: index cardinality (distinct values in a column) is the raw ingredient that produces selectivity (that count normalized by row total) — and it is a distinct concept from every other "cardinality" this course has used, hence its unique slug here.