MYSQL Select Subqueries rewrite to JOIN for performance

Hi I have two tables one called FILES and one called ACTIVITY.

There will be multiple entries of ACTIVITY for each FILE.

Here’s the table data example:

FILES:

idnamestatus
1File 1Pending
2File 2Pending
3File 3Returned

ACTIVITY:

idcreatetimefileidtypecomment
12021-06-30 15:00:301UserSome data
22021-06-30 14:02:301AdminSome data
32021-06-30 13:03:002UserSome data

I’m trying to retrieve all the data from FILES, with two additional fields per row:

  1. Highest value createtime from ACTIVITY for the corresponding FILE.
  2. Highest value createtime from ACTIVITY for the corresponding file with TYPE ‘User’.

I’ve achieved this through subqueries but now that the ACTIVITY table is > 20,000 records query times have slowed badly.

Createtime and fileid fields are indexes on ACTIVITY.

Here’s my existing SQL:

SELECT *, 
    (SELECT createtime FROM activity WHERE fileid = a.id ORDER BY createtime DESC LIMIT 1) as latestactivity,
    (SELECT createtime FROM activity WHERE fileid = a.id AND type = "User" ORDER BY createtime DESC LIMIT 1) as latestuseractivity
    FROM files AS a
    ORDER BY FIELD(status, "Pending", "Returned"), latestactivity DESC;

I’m struggling to get my head around writing this in using JOIN to reduce the query time.

Any advice or help will be much appreciated.
Thanks!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Use JOIN with GROUP BY and CASE

SELECT f.*, 
MAX(a.createtime) AS latestactivity, 
MAX(CASE
WHEN a.type = 'User' THEN a.createtime
END) AS latestuseractivity
FROM
FILES f
JOIN ACTIVITY a
ON f.id = a.fileid
GROUP BY f.id, f.name, f.status
-- ORDER BY ...


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x