Napi MySQL: update + subquery

Adott egy ilyen: 

UPDATE blabla SET akarmiid = null WHERE id IN (
  SELECT id FROM (....)
)

Elindítom HeidiSQL-ben (egyébként egész használhatónak bizonyult), egy perc múlva kb. ennyi: 

SQL Error (2013): Lost connection to MySQL server during query 
0 rows affected, 0 rows found. Duration for 0 of 1 query: 0,000 sec.

Ha kigyűjtöm az ID-ket és felsorolva beírom a WHERE id IN (...) részbe, akkor meg megcsinálja pillanatok alatt.

És ezt így hogy?

( siposa v | 2012. 08. 03., p – 14:38 )

De ugye nem ugyanott update-elsz, ahonnan a subquery select-álna?

Ha egy egy X tablaban akar updatelni olyan sorokat, amelynek az id-je megegyezik az X tablanak az id-t kivalaszto subselectjevel, akkor annak az eredemenye ugyanaz, mintha nem lenne subselect, hanem az update where feltetele ugyanaz lenne, mint a subselect where feltetele.

Matematikaval leirva:
Legyen X az a relacio, ami a tablat leirja.
Legyen S egy predikatum X-en (ez reprezentalja a subselectet). Ekkor az { x : x in X and S(x) } halmaz pont ugyanazokat az elemeket fogja tartalmazni, mint az {x' : x'.id in { x.id : x in X and S(x) } }, ha id egyedi kulcsa a tablanak. Hiszen pontosan ugyanaz az egy db S predikatum vonatkozik X-re mindket esetben, nincs mas feltetel.

UPDATE cucc 
SET fooid = null 
WHERE cuccid IN (
  SELECT cuccid 
  FROM cucc
    LEFT JOIN akarmi ON (cucc.akarmiid = akarmi.akarmiid)
  WHERE cucc.valami <> NEHANY_FUGGVENY(akarmi.valami)
)

Ezt hogyan rakom subquery nélkül where-be? Vagy, ha még meg lenne toldva néhány GROUP BY és társaival a subqueryben? (Már többször volt rá szükségem).

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Ok, lehettem volna egyertelmubb is. Azonban nem ezen van a lenyeg, hogy at lehet-e irni vagy sem*, hanem, hogy miert kezeli ennyire mostohan a subqueryket update eseten a mysql. Syntax errort nem dob ra, elvileg kepes ra, aztan meg latjuk azeredmenyet. Es ez nem az elso eset, hogy mysql+update+subqueryvel szivas van.

* Pl. ha nem kodhoz keszul a query, hanem csak szimplan modositok valamit, csinaok eloszor egy selectet, hogy ellenorizzem, mit fogok modositani es utana akore epitem az updatet, deletet. Ilyenkor edesmindegy, hogy most 0.1 vagy 3 masodpercig fut a query, de az nem, hogy 15 percet elpocsekolok a semmiert.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Nem, a subselect-et nem szereti egyaltalan. Nekem egy ilyen subselectes query 200%-on hajtotta a procit es eleg sokaig gondolkodott ugy, hogy a ket query nem is ugyanabbol a tablakbol taplalkozott.

De ez valami bug lesz az 5.1-es MySQL-ben, mert korabban az ilyen query-kkel ha nem is volt nagysagrendekkel gyorsabb (a subselectet a Postgres szereti jobban), de ennyire baja nem volt.

Kotozkodes: s/Mapi/Napi/
-- 

Ki oda vagyik, hol szall a galamb, elszalasztja a kincset itt alant. | Gentoo Portal

Ez mondjuk ezt vallja magáról: 5.0.51a-24+lenny5-log.

De itt most nem arról van szó, hogy lassabban végzi el, hanem arról, hogy sehogy. Miközben a 400 rekord updateja töredék mp alatt lemegy kigyűjtött ID-kkel.

Typo hiba fixálva.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

( siposa v | 2012. 08. 03., p – 15:16 )

MySQL 5-ben már van multi-table update, próbáld azzal.

Ez nem fog segiteni. MySQL-nel a view mindig futtatja az alatta levo selectet. A problema a fenti queryvel az, hogy a plan-je DEPENDENT subquery, amit rosszul optimalizal a MySQL, ezert a kulso tablan mindig full scan lesz. A fenti queryt joinra kell atirni ahhoz, hogy normalisan fusson. Az 5.0-s MySQL mar 2009 ota nem tamogatott. Az optimizer 5.6-ban eleg sokat fejlodott, es jol optimalizalja az ilyen queryket is. Az, hogy kozben ilyen hibauzenetet kapsz, valoszinu azt jelenti, hogy valami kegyetlenul el van konfiguralva, es kifutsz a memoriabol. Az error logot kell megnezni mi tortenik, log_warnings=2 is segithet.

( csabka v | 2015. 01. 05., h – 17:37 )

Igaz elég régi az írás, de:
MyISAM vagy InnoDB (esetleg más) engine-t használtál a táblákhoz?

Úgy tudom, hogy az InnoDB jobban kezeli a subquery-ket, lehet csak ennyi volt a gond.

Oszinten szolva fogalmam sincs mar. Nem en terveztem a DB-t (kulonben mind InnoDB PostgreSQL lett volna), de amennyire remlik vegyesen volt ossze-vissza, gondolom ahogy esik ugy puffan elv alapjan.

De ha tippelni kellene, InnoDB, mert egy par dolgot mar ujra kellett tervezni benne es szerintem ez is beleesett azok koze. Egyebkent ugyanez a kedves "MySQL has gone away" ugyanugy elojott InnoDB-vel is nekem regen meg csak nem is nagy tablakra is.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™