ORDER BY / LIMIT

-- 1) ORDER BY — multi-column, mixed direction
SELECT id, total, created_at
FROM orders
ORDER BY created_at DESC, total DESC
LIMIT 20;

-- 2) LIMIT N OFFSET M — classic pagination (degrades on large offsets)
SELECT id, title FROM posts
ORDER BY id DESC
LIMIT 20 OFFSET 1000;       -- page 51 — scans 1020 rows

-- 3) Keyset pagination — O(log N) regardless of page depth
-- Page 1
SELECT id, title FROM posts
ORDER BY id DESC
LIMIT 20;
-- Page 2 — using last id from page 1
SELECT id, title FROM posts
WHERE id < 12345
ORDER BY id DESC
LIMIT 20;

-- 4) Composite keyset (multi-column ORDER BY)
SELECT id, posted_at, title FROM posts
WHERE (posted_at, id) < ('2026-06-07 12:00', 87651)
ORDER BY posted_at DESC, id DESC
LIMIT 20;

-- 5) Top-N per group — ROW_NUMBER (MySQL 8+)
SELECT * FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) rn
    FROM orders o
) t
WHERE rn <= 3;

-- 6) Latest-per-user — fast pattern
SELECT o.* FROM orders o
JOIN (
    SELECT user_id, max(created_at) AS latest
    FROM orders
    GROUP BY user_id
) m ON m.user_id = o.user_id AND m.latest = o.created_at;

-- 7) ORDER BY without an index = filesort
-- Verify with EXPLAIN — Using filesort means MySQL sorted in-memory or to disk.
EXPLAIN SELECT * FROM posts ORDER BY views DESC LIMIT 10;
-- Add an index that matches your ORDER BY
CREATE INDEX posts_views_desc ON posts(views DESC);

-- 8) Random sampling — beware ORDER BY RAND()
-- BAD: O(N) sort
SELECT * FROM big_table ORDER BY RAND() LIMIT 5;
-- BETTER: random offset over an indexed key range
SELECT * FROM big_table
WHERE id >= FLOOR(RAND() * (SELECT MAX(id) FROM big_table))
ORDER BY id
LIMIT 5;

-- 9) Stable sort — ties resolved by adding the PK
SELECT * FROM posts ORDER BY views DESC, id DESC;     -- deterministic

-- 10) UNION + ORDER BY — wrap correctly
(SELECT id FROM a) UNION (SELECT id FROM b)
ORDER BY id DESC
LIMIT 10;

SELECT * FROM products
ORDER BY price DESC, name ASC
LIMIT 20 OFFSET 40;   -- page 3 of 20