Narrowing down and putting in order

A table has ten million rows. A screen holds twenty. Everything between those two numbers is filtering, ordering and paging.

Filtering

SELECT * FROM orders
WHERE amount BETWEEN 1000 AND 5000        -- inclusive at both ends
  AND status IN ('paid', 'shipped')
  AND customer_id IS NOT NULL
  AND notes LIKE 'urgent%';

LIKE uses % for any number of characters and _ for exactly one. The position of the wildcard decides whether an index can be used: 'urgent%' can use one, '%urgent' cannot, because an index is ordered by the start of the value.

Combining AND and OR needs brackets, and the absence of them is a common bug:

WHERE status = 'paid' AND amount > 1000 OR amount > 5000
WHERE status = 'paid' AND (amount > 1000 OR amount > 5000)

AND binds tighter than OR, so the first is not what it looks like.

Ordering

SELECT * FROM orders ORDER BY order_date DESC, id DESC;

The second column breaks ties in the first. Without a tie breaker the order among equal rows is undefined, and the database may return them differently on different runs. That is the cause of the classic paging bug where a row appears on both page 1 and page 2.

NULLs sort together, at one end or the other depending on the system, and NULLS FIRST or NULLS LAST states it explicitly.

Paging

SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 40;   -- page 3

This is correct and gets slower as the offset grows, because the database produces every skipped row and then discards it. OFFSET 500000 reads half a million rows to return twenty.

Keyset paging avoids it by remembering where the last page ended:

SELECT * FROM orders WHERE id > 1234 ORDER BY id LIMIT 20;

Constant time at any depth, because the index goes straight to the position. The cost is that you cannot jump to page 500, only forward and back, which for an infinite scroll is exactly the right trade.