Writing SQL the engine likes

CREATE TABLE t (
  phone VARCHAR(20) PRIMARY KEY,
  name  VARCHAR(50)
);

-- string column, NUMBER literal → MySQL converts the COLUMN to a
-- number for every row. full scan.
EXPLAIN SELECT * FROM t WHERE phone = 8012345678;
--   type: ALL   ← scanning

-- quote it and the index works
EXPLAIN SELECT * FROM t WHERE phone = '8012345678';
--   type: const  ← seeking

-- worse: the comparison is also WRONG.
-- '08012345678' and '8012345678' both become the number
-- 8012345678, so the leading zero silently disappears and
-- you match rows you did not mean to.

-- Postgres does not do this — it raises a type error instead,
-- which is the better behaviour.

-- O(offset) — degrades as users go deeper
SELECT * FROM posts
 ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

-- O(1) per page — seek straight to the position
SELECT * FROM posts
 WHERE (created_at, id) < (?, ?)      -- last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

CREATE INDEX idx_page ON posts (created_at DESC, id DESC);

EXPLAIN FORMAT=TREE  SELECT ...\G   -- the plan
EXPLAIN ANALYZE       SELECT ...\G   -- plan + real timings (RUNS it)
EXPLAIN FORMAT=JSON  SELECT ...\G   -- costs and filtered %
EXPLAIN FOR CONNECTION 1234\G       -- a query running RIGHT NOW

-- Postgres
--   EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;
--   BUFFERS shows shared hits vs reads — cache behaviour.

-- ① correlated subquery
SELECT c.id, c.name,
       (SELECT MAX(o.created_at) FROM orders o WHERE o.customer_id = c.id)
  FROM customers c;
--   good with few customers + an index on (customer_id, created_at)
-- ② join to a grouped subquery
SELECT c.id, c.name, o.last_order
  FROM customers c
  LEFT JOIN (SELECT customer_id, MAX(created_at) AS last_order
              FROM orders GROUP BY customer_id) o
    ON o.customer_id = c.id;
--   one pass over orders. good when most customers have orders.
-- ③ window function
WITH r AS (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY created_at DESC) rn
    FROM orders o
)
SELECT c.id, c.name, r.id AS order_id, r.created_at
  FROM customers c LEFT JOIN r ON r.customer_id = c.id AND r.rn = 1;
--   gives the whole order row, not just the date.
--   but ranks EVERY order to keep one per customer.
-- ④ LATERAL
SELECT c.id, c.name, o.id AS order_id, o.created_at
  FROM customers c
  LEFT JOIN LATERAL (
    SELECT id, created_at FROM orders
     WHERE customer_id = c.id ORDER BY created_at DESC LIMIT 1
  ) o ON TRUE;
--   stops after ONE row per customer. usually the winner
--   with an index on (customer_id, created_at DESC).
-- ⑤ anti-join: "no later order exists"
SELECT c.id, c.name, o.id, o.created_at
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
 WHERE NOT EXISTS (
   SELECT 1 FROM orders o2
    WHERE o2.customer_id = o.customer_id
      AND o2.created_at > o.created_at
 );
--   elegant, and often the slowest. measure it.