Full round replay — composite indexes

Pass: The index is sorted by a first and only by b within each a, so rows with a given b are scattered across every a block. There is no contiguous range to descend to.

Fail: Repeats the rule with no mechanism behind it.

Pass: No — different indexes with different capabilities. One serves WHERE a = ? and an ordered read of b within an a; the other serves neither.

Fail: Treats them as the same index written two ways.

Pass: Equality columns first, then the range or ORDER BY column. Seeking stops at the first range column, so anything after it filters row by row rather than narrowing.

Fail: Says most selective first, with nothing else.

Pass: Usability first. A highly selective column that no query filters on is worthless in the leading position. Selectivity breaks ties between orderings that are all usable.

Fail: Insists selectivity decides.

Pass: Not for seeking. Once b is a range, entries in that range are ordered by b and no longer grouped by c, so c is checked row by row. Reorder to (a, c, b) and all three narrow the scan.

Fail: Says all three are used because all three are in the index.

Pass: No. A full index scan also names the index. Compare rows read with rows returned — a large gap means the index was scanned rather than seeked.

Fail: Takes the plan naming the index as confirmation.

Pass: No — the narrow one is a leading prefix of the wider one, so it is redundant. Drop it and save a write per insert. An index on (status) alone would not be redundant.

Fail: Keeps both as insurance.

Pass: Reorder, or widen an existing index to cover the selected columns. Both cost nothing extra per insert, because write cost is per index rather than per column.

Fail: Adds another index first.

Pass: Bigger entries, so fewer per page, a taller index and more cache used. Good for a hot query with a short select list; wrong for SELECT *.

Fail: Says it is free because it removes reads.