CODEWITHSUNDEEP
mysqljoinsubquery
Aamir Mahmood
Aamir Mahmood

Reputation: 2724

Converting subquery to joins for performance

I have taken over a big project, and as the database is becoming large, some of the code stopped working,

Here is the query to find those rendering_requests who's last rending_log is pending, sometimes there are log entries which have no status change and recorded as noaction we dont need to count them. That is what I understood from the query.

SELECT
    COUNT(rr.rendering_id) AS recordCount
FROM
    rendering_request rr, rendering_log rl
WHERE
    rl.rendering_id = rr.rendering_id 
AND rl.status = 'pending'   AND
    rl.log_id = (
            SELECT rl1.log_id
            FROM rendering_log rl1
            WHERE 
            rl.rendering_id = rl1.rendering_id  AND 
            rl1.status = 'pending' 
            AND rl1.log_id = (
                SELECT rl2.log_id
                FROM rendering_log rl2
                WHERE rl1.rendering_id = rl2.rendering_id AND rl2.status!='noaction'
                ORDER BY rl2.log_id DESC LIMIT 1
                    )
            ORDER BY rl1.log_id DESC
            LIMIT 1
            )

for example

rendering_id=1 is having multiple logs

status=noaction
status=noaction
status=pending

and

rendering_id=2 is having multiple logs

status=noaction
status=assigned
status=noaction
status=pending

when we run this query it should display count=1 as only the rendering_id=1 is our desired record.

Right now this query has stopped working, and it hangs the mysql server

Upvotes: 0

Views: 114

Answers (1)

Kickstart
Kickstart

Reputation: 21513

Not 100% sure I have got this right, but something like this. Think you still need to use a couple of subselects but (depending on the version of MySQL) doing it this way with JOINs should be a lot faster

SELECT COUNT(rr.rendering_id) AS recordCount
FROM rendering_request rr
INNER JOIN rendering_log rl
ON rl.rendering_id = rr.rendering_id 
INNER JOIN (SELECT rendering_id, MAX(log_id) FROM rendering_log  WHERE status = 'pending' GROUP BY rendering_id) rl1
ON rl1.rendering_id = rl.rendering_id 
AND rl1.log_id = rl.log_id
INNER JOIN (SELECT rendering_id, MAX(log_id) FROM rendering_log  WHERE status!='noaction' GROUP BY rendering_id) rl2
ON rl2.rendering_id = rl1.rendering_id 
AND rl2.log_id = rl1.log_id
WHERE rl.status = 'pending'

Upvotes: 1

Related Questions