Useful MySQL Query Patterns

Most application queries fall into recurring patterns: filtered lists, aggregates, latest records, existence checks, upserts, and batch processing.

Existence Check

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

Use EXISTS when you only need to know whether a row is present.

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;

A window function can be clearer when multiple ranking rules are involved.

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);

Define the unique key that identifies the logical record. Be careful not to turn genuine duplicate events into silent updates.

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;

Run bounded batches and monitor replication lag, lock time, and application impact.

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;