CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 5 of 5 · 4 min read

Exercises

Easy

Easy 1: Define sargable (15 minutes)

State, in one sentence, what makes a predicate sargable.

Done when: the database being able to evaluate it directly using an index's structure is correctly described.

Hint

A condition the database can evaluate directly using an index's existing sorted structure.

Easy 2: Name the most common cause of non-sargability (15 minutes)

Name the most common pattern that turns a sargable condition into a non-sargable one.

Done when: wrapping the indexed column in a function is correctly named.

Hint

Wrapping the indexed column inside a function.

Easy 3: Spot the obfuscated condition (15 minutes)

State which of these two is obfuscated: WHERE price = 24.99 or WHERE price * 1 = 24.99.

Done when: WHERE price * 1 = 24.99 is correctly identified as obfuscated.

Hint

WHERE price * 1 = 24.99 is obfuscated; it matches the same rows as the simpler form but blocks direct index use.

Medium

Medium 1: Rewrite a non-sargable condition (25 minutes)

Rewrite WHERE YEAR(placed_on) = 2026 into an equivalent, sargable condition using a range.

Done when: a correct equivalent such as WHERE placed_on >= '2026-01-01' AND placed_on < '2027-01-01' is given.

Hint

WHERE placed_on >= '2026-01-01' AND placed_on < '2027-01-01'.

Medium 2: Explain why the function-wrapped version is slow (25 minutes)

Explain why WHERE YEAR(placed_on) = 2026 forces a full scan even with an index on placed_on.

Done when: the explanation correctly notes the index is sorted by the raw date value, not by the year extracted from it, so the function must be computed per row to check the condition.

Hint

The index is sorted by the actual stored date, not by any year computed from it, so the database must compute the year for every row to check the match, bypassing the index's sort order.

Medium 3: Identify a non-sargable condition in a report (30 minutes)

A report filters WHERE UPPER(customer_name) = 'ALICE' against a table with an index on customer_name. Explain why this index likely does not help, and propose a sargable alternative.

Done when: the explanation correctly identifies UPPER(customer_name) as non-sargable, with a reasonable sargable alternative proposed, such as storing or indexing a normalized-case column, or matching against the exact stored case directly.

Hint

UPPER(customer_name) wraps the indexed column in a function, making it non-sargable; a sargable alternative could store a normalized-case column and index that instead, or compare against the exact stored-case value.

Hard

Hard 1: Diagnose a slow query from its WHERE clause alone (30 minutes)

Without running EXPLAIN, predict whether WHERE placed_on + 0 >= '2026-01-01' is likely to use an index on placed_on efficiently, and explain your reasoning.

Done when: the prediction correctly identifies this as likely non-sargable or obfuscated due to the arithmetic wrapped around the indexed column, even though it is logically equivalent to the simpler, sargable WHERE placed_on >= '2026-01-01'.

Hint

Likely non-sargable or obfuscated, since the arithmetic is applied to the indexed column itself; the database may need to compute placed_on + 0 per row rather than using the index's existing order directly.

Hard 2: Explain why sargability matters even with a well-designed index (35 minutes)

A table has a well-designed composite index, correctly ordered per Level 5's guidance, but a report still performs a full scan. Explain how a non-sargable or obfuscated condition could cause this, despite the index itself being well-designed.

Done when: the explanation correctly notes that index design, covered in Level 5, and sargability, covered on this page, are separate concerns; even a perfectly designed index is useless if the query's condition is written in a form the optimizer cannot match to it.

Hint

Index design decides whether the right structure exists at all, while sargability decides whether a specific query's condition can actually make use of that structure; a condition written in a non-sargable form bypasses even the best-designed index.

Hard 3: Propose a general rule for writing index-friendly conditions (40 minutes)

Propose a general rule a team could follow when writing WHERE clauses, to reduce the chance of accidentally writing a non-sargable or obfuscated condition against an indexed column.

Done when: the proposed rule reasonably states something like: avoid wrapping an indexed column in a function or arithmetic expression within a condition, and when a transformation is needed, apply it to the comparison value instead of the column, keeping the column itself bare so the index's existing sort order remains usable.

Hint

A reasonable rule is to never wrap an indexed column in a function or arithmetic inside a condition, and instead apply any needed transformation to the comparison value, so the column itself stays bare and usable by the index.