Index Types & Selectivity | Relational Databases

In this chapter

Not every index is equally useful — we test a rare value against a common one in the exact same low-cardinality column, then build a composite index and watch column order decide whether it can be used at all.

12–15 min

The Problem in Real Life

Sarah adds an index to every column she can think of — CustomerID, Status, OrderDate, all of it. "More indexes, faster database, right?"

Mike isn't so sure. "Doesn't that Status column only have two values? Delivered or Cancelled. How much does an index even help there?"

Sarah runs the numbers and stops. He's right — and it's more subtle than she expected.

If almost everything is 'Delivered,' what is the index even skipping?

Mike

A Column That Narrows Things Down vs. One That Doesn't

Distinct-value count isn't the whole story

A column with only two values can still be highly selective for one of them, if that value is rare.

SEARCH doesn't mean fast

The query plan can report the same strategy for a highly selective filter and one that matches almost everything.

Composite indexes are ordered, not bundled

An index on (CustomerID, Status) isn't two indexes — it's one, sorted first by CustomerID, then by Status within each CustomerID.

The leading column is a hard requirement

Skip the leftmost column of a composite index in your WHERE clause, and the rest of the index can't be used for that query at all.

What Makes an Index Actually Useful?

Selectivity is how much a condition actually narrows down the rows that match it. A highly selective condition matches a small fraction of the table; a poorly selective one matches most of it — and an index is only genuinely useful when it lets the database skip a large share of the rows it would otherwise have to check.

It's tempting to judge selectivity purely by how many distinct values a column has. That's a reasonable starting point, but it isn't the whole story — what actually matters is how many rows a specific value matches, and that can vary wildly even within the same column.

CustomerID has 500 distinct values spread across 10,000 rows — any single value matches only about 20 rows. This is exactly the kind of column an index earns its keep on.

High Selectivity: CustomerID

CREATE INDEX idx_allorders_customerid ON AllOrders (CustomerID);

EXPLAIN QUERY PLAN
SELECT * FROM AllOrders WHERE CustomerID = 250;

SEARCH AllOrders USING INDEX idx_allorders_customerid (CustomerID=?) — a clean, effective index lookup.

Status only has two possible values — 'Delivered' or 'Cancelled' — which sounds like a bad candidate for an index. But 'Cancelled' only actually happens on 1% of orders, so filtering for it is still genuinely selective, even though the column itself has almost no variety.

A Rare Value in a Low-Cardinality Column

CREATE INDEX idx_allorders_status ON AllOrders (Status);

EXPLAIN QUERY PLAN
SELECT * FROM AllOrders WHERE Status = 'Cancelled';

SELECT COUNT(*) AS CancelledRows FROM AllOrders WHERE Status = 'Cancelled';

SEARCH AllOrders USING INDEX idx_allorders_status (Status=?) — and CancelledRows comes back as just 100 out of 10,000.

Same column, same index — but filtering for 'Delivered' instead. The plan reports the exact same strategy as the 'Cancelled' query above, yet this one matches nearly the entire table.

The Same Index, the Common Value

EXPLAIN QUERY PLAN
SELECT * FROM AllOrders WHERE Status = 'Delivered';

SELECT COUNT(*) AS DeliveredRows FROM AllOrders WHERE Status = 'Delivered';

SEARCH AllOrders USING INDEX idx_allorders_status (Status=?) again — but DeliveredRows comes back as 9,900 out of 10,000. The label looks identical; the real work behind it is nothing alike.

A composite index covers more than one column, in a fixed order. This one is built on (CustomerID, Status) — CustomerID leading, Status second — so it can serve queries that filter on CustomerID alone, or on both columns together.

A Composite Index — Column Order Matters

DROP INDEX idx_allorders_status;

CREATE INDEX idx_allorders_customer_status ON AllOrders (CustomerID, Status);

EXPLAIN QUERY PLAN
SELECT * FROM AllOrders WHERE CustomerID = 250 AND Status = 'Delivered';

SEARCH AllOrders USING INDEX idx_allorders_customer_status (CustomerID=? AND Status=?) — both conditions used in a single index lookup.

The composite index is still there — but a query that only filters on Status, without mentioning CustomerID, can't use it. A composite index only helps when the query includes its leading (leftmost) column.

The Same Composite Index, Filtering on the Second Column Alone

EXPLAIN QUERY PLAN
SELECT * FROM AllOrders WHERE Status = 'Cancelled';

SCAN AllOrders — with the standalone Status index dropped, there's no usable index left for this particular query.

The lesson isn't "low-cardinality columns never deserve an index" — Status proved that wrong for the rare value. It's that selectivity has to be judged per query, against how many rows an actual condition matches, not just by counting how many distinct values a column has.

And a composite index isn't a bundle of independent shortcuts — it's one sorted structure, ordered by its columns left to right. Leave out the leading column, and the rest of the index is invisible to that query, no matter how useful it looks on paper.

Key Takeaway

EXPLAIN QUERY PLAN's SEARCH label only tells you an index was used — it doesn't tell you how many rows it actually matched, so a column's true selectivity depends on the specific value being filtered for, not just how many distinct values the column has.

Why This Matters

Blindly indexing every column is a common, costly mistake — each unused or poorly-selective index still slows down every write without meaningfully speeding up reads. Understanding selectivity is what separates an index that earns its cost from one that's just dead weight.

GreenMart now has indexes that actually pay for themselves, and a composite index shaped around how the reports actually query the data. Next: what SQLite is really deciding when it builds a query plan in the first place.