Course contentsShow
← System Design
Lesson 1895 of 1,91945. API Design and VersioningFree lesson

Default Sorting and Index Alignment

Choosing sensible default sort orders that align with database indexes for performance.

Default Sorting and Index Alignment

What you'll learn: How choosing the right default sort order can dramatically improve API performance by leveraging database indexes.


When you design an API that returns sorted data, the default sort order isn't just a user experience choice—it's a performance decision. Your API's default sort should align with an existing database index to avoid expensive full-table scans and in-memory sorting.

Why This Matters

Imagine a library where books are physically shelved by call number. If patrons usually ask for "books sorted by call number," you can walk straight to the shelf. But if they ask for "books sorted by color," you'd need to scan every shelf, pull every book, and sort them manually. Databases work the same way.

When your API sorts by a field that has an index (especially the primary key or a clustered index), the database can return results in that order directly from disk—blazingly fast. When you sort by an unindexed field, the database must:

  1. Scan the entire table
  2. Load rows into memory
  3. Perform an expensive sort operation

Making the Right Choice

Choose your default sort based on:

  • Primary access patterns: How do users typically need the data ordered?
  • Index structure: What indexes already exist or can be added efficiently?
  • Temporal data: For time-series data, sorting by created_at DESC is common and should have an index on created_at

For example, if you're returning a list of orders, defaulting to ?sort=-created_at (newest first) is natural for users and aligns with an index on (created_at DESC).

You can still support other sort options via query parameters (like you learned in "Sorting Query Parameters"), but make the default the fastest path.

Key Takeaway: Always align your API's default sort order with an existing database index to ensure queries execute efficiently at scale without full-table scans or in-memory sorting.