Bug #24770 update with subquery slow
Submitted: 2 Dec 2006 0:37 Modified: 21 Mar 2011 4:21
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: In progress Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.1 OS:Any (all)
Assigned to: Roy Lyseng CPU Architecture:Any
Tags: qc, semijoin, update subquery

[2 Dec 2006 0:37] Roberto Spadim
Description:
here is an peace of php code :

UPDATE rastreabilidade SET rastreabilidade_pallet=\"$RASTREABILIDADE_PALLET\" WHERE codigo_barra IN (
SELECT codigo_barra FROM pdt_pallet_box_montagem WHERE unidade_id=$pdt_unidade_id AND centro_id_red=$pdt_centro_id AND box=$BOX
)

is slower than:

fetch this query on array:
SELECT codigo_barra FROM pdt_pallet_box_montagem WHERE unidade_id=$pdt_unidade_id AND centro_id_red=$pdt_centro_id AND box=$BOX

and after 
UPDATE rastreabilidade SET rastreabilidade_pallet=\"$RASTREABILIDADE_PALLET\" WHERE codigo_barra IN (
values_from_fetched_array
)

How to repeat:
i will post create table as files

Suggested fix:
maybe optimizer problem?!
[3 Dec 2006 10:25] Valeriy Kravchuk
This is a well know problem with subqueries. Will be fixed in MySQL 5.2 (work in progress already).
[24 Jan 2011 8:33] Manyi Lu
This bug should be closed when WL#3985 WL#1110 WL#3751 WL#2980 have been merged into trunk/5.6.x.
[21 Mar 2011 3:56] Roberto Spadim
nice, i will wait next version
[21 Mar 2011 4:12] Roberto Spadim
i will close bug: http://bugs.mysql.com/bug.php?id=36660

since bug 36660 is something similar to this one
[21 Mar 2011 4:16] Roberto Spadim
closed bug 39433 since it's similar too
[21 Mar 2011 4:17] Roberto Spadim
bug 48763 closed too
[21 Mar 2011 4:21] Roberto Spadim
bug 57146 closed too (i'm making a big garbage collector heeheh)