Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Which columns are worth an index · SQL scenario · D8LooP

Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.

CODE WORKSPACE

Which columns are worth an index

A team is about to add six indexes to the orders table because six different queries felt slow. Before anyone runs a migration, work out which columns an index could actually help with. Report each column on orders with its selectivity as a percentage and a verdict. Order by selectivity_pct descending, then by column_name.

Result columns · in this order

`column_name`	The column.
`selectivity_pct`	Distinct values as a percentage of rows, to two decimal places.
`verdict`	'worth indexing' at or above 1%, otherwise 'too few distinct values'.

How to approach it

Divide distinct values by total rows in decimal arithmetic, then band the result with a CASE.

Sample input

column_statsOne row per column the database keeps statistics for. Row counts and distinct counts as of the last stats refresh.

table_name	column_name	total_rows	distinct_values	null_rows
orders	order_id	2000000	2000000	0
orders	customer_id	2000000	410000	0
orders	status	2000000	6	0
orders	created_at	2000000	1740000	0
orders	is_test	2000000	2	0
orders	coupon_code	2000000	1200	1880000
order_items	order_id	9400000	2000000	0
order_items	sku	9400000	84000	0
customers	customer_id	410000	410000	0
customers	country	410000	42	900
customers	email	410000	409800	200
customers	signup_source	410000	9	0

12 rows — scroll inside the table to see them all.

Expected output

The exact result your query should return for this input.

column_name	selectivity_pct	verdict
order_id	100	worth indexing
created_at	87	worth indexing
customer_id	20.5	worth indexing
coupon_code	0.06	too few distinct values
is_test	0	too few distinct values
status	0	too few distinct values

6 rows — all rows shown.

Constraints

1Selectivity is distinct_values / total_rows, expressed as a percentage and rounded to two decimal places.
2Both operands are integers and this engine truncates integer division, so promote to decimal before dividing — 100.0 * distinct / total, not 100 * (distinct / total).
3The verdict is worth indexing at 1% or above and too few distinct values below it. One percent is a working rule of thumb, not a law — the prompt below says what it misses.
4Report every column on orders, including the ones that fail. The failures are the point of the exercise.
5Order by selectivity_pct descending, then column_name.

Worked example

order_id has 2,000,000 distinct values in 2,000,000 rows — 100% selectivity, and a lookup on it returns one row. created_at is 87%. customer_id is 20.5%, which is still a hundred-fold reduction and well worth an index. status has six distinct values in two million rows: 0.00% after rounding. An index on it points at roughly a third of the table for a common value, and the engine will usually prefer a full scan anyway. The rule of thumb has a real exception. coupon_code scores 0.06% but is NULL in 1,880,000 of the 2,000,000 rows — the 120,000 rows that have one are spread across 1,200 codes, about a hundred rows each. A partial index (WHERE coupon_code IS NOT NULL) on that column is small and very effective, which is why the verdict column is a starting point rather than a decision.

What this tests

Whether you can tell a column an index will help from one it will not, and whether the integer-division trap catches you in a ratio.

SQL

AI evaluation

The reviewer reads your query.

Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.

Desktop only

This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.

Read the library instead Back to modules