ORDER BY và Index - Tránh bước sort bổ sung bằng mọi giá

Index không chỉ giúp tìm nhanh - nó còn trả kết quả ĐÃ SORTED. Thêm cột sort vào cuối index (sau các cột WHERE) → database đọc từ index ra đã đúng thứ tự → không cần sort thêm.

SELECT * FROM issues
WHERE type = 'bug'
ORDER BY severity DESC, created_at DESC;
 
-- ✅ Index: (type, severity DESC, created_at DESC)
-- 1. Fast lookup: type='bug'
-- 2. Scan descending → entries đã sorted theo severity, created_at
-- 3. Trả về trực tiếp → KHÔNG cần sort bổ sung!

Vì sao tránh sort lại quan trọng đến vậy?

Khi database phải sort kết quả mà index không hỗ trợ:

1. Tất cả matching rows phải load vào memory trước
2. Fit trong memory → in-memory sort (nhanh)
3. KHÔNG fit (vượt sort_buffer_size / work_mem) → disk-based sort: ghi data ra file tạm theo chunk → sort từng chunk → merge → lặp lại nếu data quá lớn

Disk-based sort có thể biến query 10ms thành 10 giây. Ngay cả với SSD.

Tips config:

• MySQL: sort_buffer_size mặc định 256KB - rất nhỏ! Tăng lên 4-8MB cho production.
• PostgreSQL: work_mem mặc định 4MB → 32-64MB nếu hay sort nhiều data. Cẩn thận: apply per-operation, nhiều concurrent queries có thể ăn hết RAM.

Bẫy LIMIT

ORDER BY ... LIMIT 10 KHÔNG tự nhanh - database vẫn sort toàn bộ matching rows trước rồi mới lấy top 10. Chỉ index mới giúp tránh sort hoàn toàn: scan 10 entries đầu từ index và dừng.

Sort nhiều cột với hướng khác nhau

B-tree scan được cả 2 hướng, nên ORDER BY col ASC hay DESC đều dùng chung 1 index. Nhưng hướng trộn lẫn cần index khai báo đúng:

SELECT * FROM highscores ORDER BY score DESC, created_at ASC LIMIT 10;
-- ❌ Index (score, created_at) mặc định: scan backward → created_at cũng DESC!
-- ✅ CREATE INDEX ON highscores (score DESC, created_at ASC);

Ví dụ tổng hợp WHERE + ORDER BY

SELECT * FROM products
WHERE category_id = 5 AND in_stock = true
ORDER BY price ASC LIMIT 20;
 
-- ❌ (category_id, in_stock): filter OK, nhưng phải sort 50,000 products
-- ❌ (price): sort OK, nhưng phải filter TOÀN BỘ products
-- ✅ (category_id, in_stock, price): phễu → scan price ascending → lấy 20
--    Tổng: chỉ đọc ~20 index entries. Từ nhiều giây xuống <1ms!

Liên quan

• Quét một hướng - nền tảng của sorted output
• SQL Execution Order - vì sao cột WHERE đứng trước cột ORDER BY
• Index Selection - Xung đột Filter và Sort - khi DB phải chọn giữa lọc và sắp xếp
• Keyset Pagination - phân trang tận dụng index order