CODEWITHSUNDEEP
mysql
Ben Sanjurgo
Ben Sanjurgo

Reputation: 15

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:

id name status
1 File 1 Pending
2 File 2 Pending
3 File 3 Returned

ACTIVITY:

id createtime fileid type comment
1 2021-06-30 15:00:30 1 User Some data
2 2021-06-30 14:02:30 1 Admin Some data
3 2021-06-30 13:03:00 2 User Some 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!

Upvotes: 0

Views: 69

Answers (1)

Son Tran
Son Tran

Reputation: 116

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 ...

Upvotes: 2

Related Questions