Re: slow down on UPDATE using IN statements

On Mon, 3 Nov 2003, [iso-8859-1] MaRcElO PeReIrA wrote:

> Hi there,
>
> I was in troubles with a UPDATE+IN statement:
>
> The following command use to take about 5 minutes to
> be done:
>
> UPDATE requisicao SET conclusao='3' WHERE reg IN
> (SELECT reg FROM requisicao WHERE now()-data>'15
> days');
>
> The table 'requisicao' has only about 400 lines (!!).
>
> If I change it to:
>
> UPDATE requisicao SET conclusao='3' WHERE reg IN (12,
> 45, 87, 98, 129, 350, 389);
>
> I have detected that the major problem isn't in the
> amount of lines changed, but in the subselect.
>
> How can I solve/optimize it? I would like to use the
> IN, but in the last case I would make a software
> change.

The easiest is wait for 7.4 where IN optimizes better than it has in the
past and see if that resolves the problem, otherwise, try
changing the query into an exists form.