High SQL Question 165 of 226

How would you index a filter-and-sort API: category, price range, and created_at desc?

Java Specialist · Speak this in 60–90 seconds · Faridabad & Delhi NCR

PICTURE THIS: DATABASE INDEX

Without indexScan every row

With indexJump to keys

CostWrites slower

Simple meaning

A composite index that matches equality then range then order, such as (category_id, price, created_at), can serve the query without a sort if the planner agrees.

WHY — SQL instead of guessing?

Why interviewers care about SQL:

This is a process

question about SQL.

Panels listen for order,

trade-offs, and what you would actually do on a Backend project - not buzzwords.

Stay structured

Name the idea, why it exists, then one short example.

Close cleanly

End with when you use it and one common pitfall.

STEPS — What happens step by step?

Before you speak the answer, walk the interviewer through these steps:

1
A composite index that
matches equality then range then order, such as (category_id, price, created_at), can serve the query without a sort if the planner agrees.
2
If users sort by
many columns, you cannot cover every permutation
3
pick the hottest and
consider a search engine.
4
Measure with EXPLAIN on
production-like data, not ten demo rows.
5
Common mistake
What juniors usually get wrong.
6
Close
When you pick this over the alternative.

EXAMPLE — See it in action

Here's a short line you can speak, broken into clear beats:

Say this line

“If users sort by many columns, you cannot cover every permutation”

Break into beats

Ifuserssortbymanycolumns

Speaking order

2987408337471632900

Note: Adapt this scaffold to your own project — keep it under 60–90 seconds.

Key takeaway

A composite index that matches equality then range then order, such as (category_id, price, created_at), can serve the query without a sort if the planner agrees. If users sort by many columns, you cannot cover every permutation