Improvement of SQL UPDATE
<p>I need improvement of this SQL UPDATE query. The query has to only update the first row, but I suspect that it uses a lot of power on running through all rows and running the subquery for all rows, because when using big databases it it very slow. Any suggestions?</p>

<pre><code>    UPDATE leads l
    SET status='processing', processor='$processor' 
    WHERE pool IN ($pools) AND status='active'
    ORDER BY (SELECT count(*) FROM calls c WHERE c.lead = l.id), id ASC
    LIMIT 1
</code></pre>

<p>UPDATE</p>

<p>The slowness is indeed caused by the subselect. Typical there might be 2000-3000 matches in the leads-table. The easiest way would be to build in a column in the leads-table that counts calls manually so I wouldn't need the subselect, but it is a running site where these changes need to be made, so it would mess up the order if I added the column now.</p>

<p>But I suppose it is the only way to avoid the large subselect.</p>