CODEWITHSUNDEEP
mysqloptimizationsubquery
S.Walker
S.Walker

Reputation: 166

Optimizing a sub-sub-query in MySQL

I have a request ticketing system that allows for commenting on the tickets. I've added a feature that shows the user upon login the most recent comment to any of their tickets as kind of a clue if anything has changed since their last visit. I can accomplish this with a sub-sub-select which runs fine with a small database but I suspect it will not scale well over time.

-- stage 1: all user's request ticket ids
SELECT id FROM requests WHERE username='someuser';

-- stage 2: most recent comment on all user's request ticket ids
SELECT
  MAX(commented_at)
FROM
  requests_comments
WHERE
  request_id IN (
    -- stage 1
    SELECT id FROM requests WHERE username='someuser'
  );

-- final stage: whole comment row of any user's most recent comment
-- on all of selected user's request tickets
SELECT
  *
FROM
  requests_comments
WHERE
  commented_at=(
    -- stage 2
    SELECT
      MAX(commented_at)
    FROM
      requests_comments
    WHERE
      request_id IN (
        -- stage 1
        SELECT id FROM requests WHERE username='someuser'
      )
  );

I've tried messing around with GROUP BY and HAVING but haven't gotten where I need to be.

Also, the likelihood of having multiple comments within the same timestamp is infinitesimal. And the id keys and foreign keys are auto-assigned and thus cannot be trusted that a higher id is more recent than a lower id.

How can I optimize my query to ensure performance over time?

Upvotes: 0

Views: 22

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you want only the most recent comment for a given user, then you can express this as:

SELECT rc.*
FROM requests r JOIN
     requests_comments rc
     ON rc.request_id = r.id
WHERE r.username = 'someuser'
ORDER BY rc.commented_at DEC
LIMIT 1;

For performance, you want an index on requests(username, id) and request_comments(request_id).

Upvotes: 2

Related Questions