1

I have 2 tables events and event_status. The event_status table is the history table, and latest being the current one. The events and event_status have a one-to-many mapping. I need a optimized query to find the event and its current status.

I am using MySQL btw.

Eg: events table

ID NAME
1 Event 1
2 Event 2

event_status

id event_id status created_at
1 1 Proposal Accepted 2023-01-10 13:40:01
2 1 Planning 2023-01-10 13:55:26
3 1 Cancelled 2023-02-05 09:00:00
4 2 Started 2023-02-05 09:00:00

Output I need>

ID NAME STATUS
1 Event 1 Cancelled
2 Event 2 Started

I looked into multiple ways to generate the data. Some queries that I tried are:
1. Removing the old status using subquery in where

SELECT e.*, es.status
FROM events e
         JOIN
     event_status AS es
     ON e.id = es.event_id
         AND es.id = (SELECT id
                      FROM event_status es2
                      WHERE es2.event_id = e.id
                      ORDER BY es2.created_at DESC
                      LIMIT 1); 
  1. Finding the smaller status during join and filtering on where.
SELECT
    e.ID,
    e.NAME,
    es.status AS STATUS
FROM
    events e
JOIN
    event_status es ON e.ID = es.event_id
LEFT JOIN
    event_status es2 ON es.event_id = es2.event_id AND es.created_at < es2.created_at
WHERE
    es2.event_id IS NULL;
  1. Getting max created_at for each event and adding extra join with normal join.
SELECT
    e.ID,
    e.NAME,
    MAX(es.status) AS STATUS
FROM
    events e
JOIN
    event_status es ON e.ID = es.event_id
JOIN (
    SELECT
        event_id,
        MAX(created_at) AS latest_created_at
    FROM
        event_status
    GROUP BY
        event_id
) AS latest_status ON es.event_id = latest_status.event_id AND es.created_at = latest_status.latest_created_at
GROUP BY
    e.ID, e.NAME;
  1. Using window function to get the row id and selecting the top row
WITH Status_row_id AS (
    SELECT
        es.event_id,
        es.status,
        ROW_NUMBER() OVER (PARTITION BY es.event_id ORDER BY es.created_at DESC) AS row_id
    FROM
        event_status es
)
SELECT
    e.ID,
    e.NAME,
    rs.status AS STATUS
FROM
    events e
JOIN
    Status_row_id rs ON e.ID = rs.event_id
WHERE
    rs.row_id = 1;

I need some help in choosing the best query, or strategies to make the qurey faster.

2
  • 1
    Have you tested using the explain plan ? Do you have indexes (event_id ,created_at DESC) in event_status table and (ID) on events table ? Which one the four queries is faster on your environment ? Commented Oct 1, 2023 at 11:38
  • I agree with @ErgestBasha - you need to test in your own environment. Off the top of my head, I would go with the window function - they were developed for this very task so I can only assume that there are optimisations in place (but this is MySQL).
    – Vérace
    Commented Oct 1, 2023 at 11:45

1 Answer 1

0

Groupwise-max is messy to query. Let's revamp the system to make it simpler.

Let's change events_table to events_current and add columns for the current status of the event.

Anc change the name events_status to event_history, since it really has the "history", not the "current status".

Current would be updated (actually use IODKU, so that the same action can create a new event). When adding a new row to history also update current. Note that your specific request (in this Question) is easily handled via a SELECT ... FROM current ....

History would be essentially as you have it now. It is INSERT-only, there would be no UPDATE. You would SELECT from it only if you needed "old" info that is not in current.

If you don't want to switch to that schema design, see the [greatest-n-per-group] tag or Groupwise-Max

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.