How to restructure this slow query

Question

For the following query:

SELECT l.*
FROM `leads` AS `l`
INNER JOIN `users` AS `u` ON l.client_id = u.id
WHERE (`l`.`lender_id` = 1)
AND (l.claimed_date IS NULL)
AND (`l`.`disabled` = 0)
AND (u.last_activity_date > '2012-01-13 02:42:10')
AND (l.id = (
    SELECT `leads`.`id`
    FROM `leads`
    WHERE (lender_id = l.lender_id)
    AND (client_id = l.client_id)
    AND (disabled = 0)
    ORDER BY `price` DESC
    LIMIT 0, 1
))
GROUP BY `l`.`client_id`
ORDER BY `u`.`last_activity_date` DESC

How could we restructure the query to remove the correlated subquery?

Answer 1

You could avoid the correlated subquery with a filtering join, like:

select  *
from    leads l
join    users u
on      l.client_id = u.id
join    (
        select  lender_id
        ,       client_id
        ,       max(price) as max_price
        from    leads
        where   disabled = 0
        group by
                lender_id
        ,       client_id
        ) tl
on      tl.lender_id = l.lender_id
        and tl.client_id = l.client_id
        and tl.max_price = l.price
WHERE   l.lender_id = 1
        and l.claimed_date is null
        and l.disabled = 0
        and u.last_activity_date > '2012-01-13 02:42:10'
group by
        l.client_id

If there is more than one lead with the highest price, this version will return all of them.

Answer 2

If I understand correctly, you are trying to have a "greatest-n-per-group" query. Check this version:

SELECT l.*
FROM users AS u 
  INNER JOIN leads AS l 
    ON l.id = 
       ( SELECT id
         FROM leads
         WHERE client_id = u.id
           AND lender_id = 1
           AND claimed_date IS NULL
           AND disabled = 0
         ORDER BY price DESC
         LIMIT 0, 1
       )
WHERE (u.last_activity_date > '2012-01-13 02:42:10')
ORDER BY u.last_activity_date DESC