Useful MySQL Query Patterns

After enough years of business apps, you notice that almost every query is one of a handful of patterns: filtered lists, totals, "the latest one for each," "does this exist," upserts, and batch jobs. These are the versions I keep reaching for.

Existence check

SELECT EXISTS (
    SELECT 1
    FROM users
    WHERE email = :email
) AS email_exists;

If you only need to know whether a row exists, use EXISTS. Don't COUNT(*) the whole thing just to check if it's more than zero.

Latest child record per parent

SELECT t.*
FROM trip_status_history AS t
JOIN (
    SELECT trip_id, MAX(id) AS latest_id
    FROM trip_status_history
    GROUP BY trip_id
) AS latest ON latest.latest_id = t.id;

On MySQL 8, a window function is often clearer, especially if there's more than one tiebreaker.

WITH ranked AS (
    SELECT
        h.*,
        ROW_NUMBER() OVER (
            PARTITION BY trip_id
            ORDER BY created_at DESC, id DESC
        ) AS row_num
    FROM trip_status_history AS h
)
SELECT *
FROM ranked
WHERE row_num = 1;

Conditional aggregates

SELECT
    COUNT(*) AS total,
    SUM(status = 'open') AS open_count,
    SUM(status = 'closed') AS closed_count,
    SUM(priority = 'urgent' AND status != 'closed') AS urgent_open_count
FROM tickets;

Upsert

INSERT INTO vehicle_telemetry (
    vehicle_id,
    recorded_at,
    latitude,
    longitude
) VALUES (
    :vehicle_id,
    :recorded_at,
    :latitude,
    :longitude
)
ON DUPLICATE KEY UPDATE
    latitude = VALUES(latitude),
    longitude = VALUES(longitude);

This depends on a unique key that really identifies the record. Be careful, though: if two events are genuinely separate, an upsert will quietly squash them into one.

Keyset pagination

SELECT id, created_at, subject
FROM tickets
WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Batch updates

UPDATE notifications
SET archived_at = UTC_TIMESTAMP()
WHERE archived_at IS NULL
  AND created_at < UTC_TIMESTAMP() - INTERVAL 90 DAY
ORDER BY id
LIMIT 1000;

Do big updates in small batches, and watch replication lag, lock waits, and whether the app slows down. One giant UPDATE on a busy table is a great way to ruin everyone's afternoon.

Anti-join: find missing relationships

SELECT c.id, c.name
FROM customers AS c
LEFT JOIN contacts AS ct ON ct.customer_id = c.id
WHERE ct.id IS NULL;