How to restructure this slow query 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 &gt; '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?
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 &gt; '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.