We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design Masterclass · Data Governance Compliance
Free preview: the first 2 parts of this 6-part lesson.
Already bought the course? Sign in to read the whole lesson.
this lesson is part of
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design courseread the rest of this lesson in
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design coursein Data Governance Compliance
This lesson is part of the full System Design Masterclass. Unlock it along with 769 other lessons for a one-time payment.
One-time payment by card or PayPal
Get Lifetime AccessOne-time payment: UPI, card or netbanking
Get Lifetime Access18 Foundation lessons are free, no payment needed. Try them now
It is easy and good
“It is really nice and good Please join the course”
Open any e-commerce site. The default product listing shows items sorted by "relevance," a carefully tuned scoring algorithm. Switch to "Price: Low to High" and the same 500 products rearrange instantly. Switch to "Newest" and they rearrange again.
Sorting is so ubiquitous that users expect it everywhere. Email sorted by date. Search results sorted by relevance. Leaderboards sorted by score. Bank transactions sorted by amount. The data is the same, but the order changes what users see first, and what users see first determines what they click, what they buy, and how they perceive the product.
Behind the scenes, sorting has real engineering consequences. Sorting 100 records is trivial. Sorting 100 million records requires database indexes, careful query optimization, and sometimes pre-computed sort orders. Amazon's product search team discovered that adding a B-tree index on their price column reduced sort time from 2.3 seconds to 4ms on their catalog tables. That is a 575x improvement from a single CREATE INDEX statement.
| Sort Type | What It Does | |
|---|---|---|
| Single column | Sort by one field | ORDER BY created_at DESC |
| Multi-column | Primary sort, then secondary for ties | ORDER BY status ASC, created_at DESC |
| Custom order | Non-alphabetical, non-numeric | ORDER BY CASE status WHEN 'urgent' THEN 1 WHEN 'high' THEN 2 ... |
| Collation | Language-aware string sorting | ORDER BY name COLLATE "en_US" (handles accents, case) |
| Null handling | Where nulls appear | ORDER BY price NULLS LAST |
Sorting and pagination go together. You almost never sort an entire table. You sort and then take a page (LIMIT/OFFSET or cursor-based). The sort order must be stable: given the same data, the same query must produce the same order.
The unstable sort problem: If 50 products have the same price, ORDER BY price can return them in any order, and the order might change between queries. A user on page 2 might see products that were also on page 1. Fix: ORDER BY price, id gives a stable, deterministic order because id is unique and breaks all ties.
Postgres's EXPLAIN ANALYZE will show Sort Method: external merge Disk when the sort buffer overflows. If you see that, either add an index or increase work_mem. MySQL calls this "filesort" in its query plan.