Thomas: MySQL / Update mit Subselect

Hi there

ich steh grad tierisch auf der leitung und begreif die welt nicht mehr :(
vielleicht könnt ihr mir ja weiter helfen.

wir haben folgenden tatbestand:

  • ich habe eine tabelle "tab1" mit 200k rows
  • eine weitere tabelle "tab2" mit etwa 100 rows
  • ich will alle rows der tab1 updaten, deren id in tab2 vorhanden ist
  • das ergebnis vom subselect ist nicht von tab1 abhängig (der subselect müsste nur einmal ausgeführt werden)
  • id und id2 sind natürlich im index ;)

und so sieht der update aus: (vereinfachte version)
update tab1 set feld = 1
where  id in (select id from tab2 where id2 = 123)

dieser update dauert 200ms - viel zu lange!
ich habe das gefühl dass der subselect für alle 200k rows ausgeführt wird. wieso weiss kein mensch, denn das ergebnis ist immer das selbe. wie soll es auch anders sein wenn es keine verknüpfung zur übergeordenten tabelle gibt.
richtig wäre doch: subselect ausführen und dann mit den id auf tab1 zugreifen. das wäre mindestens faktor 1000 schneller.
ich kanns jetzt grad nicht testen, aber ich wette oracle würds so
machen.

anyway, wie kann ich diesen update optimieren?
eine lösung hät ich schon, aber das ist mir zu unschön:
ich könnte eine temptable füllen und diese im subselect ohne where-bedingung selektieren. dann merkt das kluge mysql nämlich dass es den subselect nur einmal ausführen muss. ^^

ps: meine mysql-version: 5.0.45-community-nt

  1. yo,

    update tab1 set feld = 1
    where  id in (select id from tab2 where id2 = 123)

    was mir dabei auffällt ist, dass du die spalten nicht mit dem tabellennamen oder einem alias gepräfixt hast. offensichtlich kommt zumindestens die spalte id in beiden tabellen vor.

    des weiteren sind 200 ms ja nun nicht die welt, was sagt den der ausführungsplan dazu ?

    Ilja

    1. was mir dabei auffällt ist, dass du die spalten nicht mit dem tabellennamen oder einem alias gepräfixt hast. offensichtlich kommt zumindestens die spalte id in beiden tabellen vor.

      also wenns ein alias-problem gäbe, würd er reklamieren. daran kanns nicht liegen.

      des weiteren sind 200 ms ja nun nicht die welt, was sagt den der ausführungsplan dazu ?

      200ms ist nicht viel wenn man den update 1mal pro jahr ausführt. aber er (oder ein ähnlicher update) wird pro tag an die 200 mal ausgeführt. und besonders ärgerlich ists wenn man schon an der performance-grenze läuft und man weiss das der update gut programmiert in 1-2 ms durch wäre.
      ausführungsplan? meinst du explain? der sagt mir dass bei tab1 alle sätze gescannt werden.

      kann mir denn keiner helfen?
      muss wohl doch die arbeit doppelt machen (mit temptable) und mir die scripte vermiesen damits mysql schnallt...

      1. yo,

        also wenns ein alias-problem gäbe, würd er reklamieren. daran kanns nicht liegen.

        ich würde trotzdem einen prefix setzen, es ließt sich einfach besser und kleine änderungen können manchmal auch wunder bewirken, vor allem was tuning betrifft.

        ausführungsplan? meinst du explain? der sagt mir dass bei tab1 alle sätze gescannt werden.

        dem solltest du auf die spur gehen, warum er das macht, es kann ein fehlender index sein oder aber bei oracle könnten es sein, dass die tabellen und indexe nicht analysiert wurden. ich kann zu mysql nicht all zuviel sagen, eventuell gibt es ähnlich wie bei oracle einen hint, wo du dem dbms anweisen kannst, einen vorhanden index auch zu benutzen. tuning bedeutet vieles ausprobieren und sich immer wieder den ausführungsplan anschauen dabei.

        Ilja

        1. n'abend,

          dem solltest du auf die spur gehen, warum er das macht, es kann ein fehlender index sein oder aber bei oracle könnten es sein, dass die tabellen und indexe nicht analysiert wurden. ich kann zu mysql nicht all zuviel sagen, eventuell gibt es ähnlich wie bei oracle einen hint, wo du dem dbms anweisen kannst, einen vorhanden index auch zu benutzen. tuning bedeutet vieles ausprobieren und sich immer wieder den ausführungsplan anschauen dabei.

          Index Hinting kennt MySQL seit 4.0.x - dabei kann man auf Indice hinweisen (USE) verbieten (IGNORE) und bestimmen (FORCE).

          MySQL nutzt einen Index dann nicht, wenn es das Gefühl hat, dass ein Table-Scan die schnellere Variante ist. MySQL nutzt

          weiterhin schönen abend...

          --
          #selfhtml hat ein Forum?
          sh:( fo:# ch:# rl:| br:> n4:& ie:{ mo:} va:) de:] zu:} fl:( ss:? ls:[ js:|
          1. yo,

            MySQL nutzt einen Index dann nicht, wenn es das Gefühl hat, dass ein Table-Scan die schnellere Variante ist.

            mit gefühlen hat das aber wenig zu tun, sondern mit informationen, die dem dbms zur verfügung stehen und wie es darauf den ausfürhungsplan aufbaut. deswegen mien hinweis, dem auf die spur zu gehen und wennd as dbms es nicht versteht, dass es mit index schneller ist als ohne, einen hint setzen.

            Ilja

        2. dem solltest du auf die spur gehen

          jo, genau das hab ich gemacht in dem ich mir hier an die community gewannt habe. ;)
          mit index hat mein problem allerdings überhaupt nichts zu tun.
          ich wette, der index wird sogar verwendet, sonst würds deutlich mehr als 200ms dauern...
          das problem ist/war doch folgendes: der subselect ist statisch. er müsste nur einmal ausgeführt werden und mit dem zurückgelieferten ergebnis auf die übergeordnete tabelle zugegriffen werden. da kannst du noch so viele indizes haben, wenn die engine zu blöd ist nützt das nix.
          die lösung hat mir thomas (der auf der linken schulter) gestern abend gegeben. mann muss den subselect in den form-block nehmen.
          ist zwar nicht sehr elegant, aber immerhin faktor 10 mal schneller.
          ok, es könnte nochmal 10 mal schneller möglich sein, aber das ist mir jetzt erst mal egal.

  2. update tab1 set feld = 1
    where  id in (select id from tab2 where id2 = 123)

    versuchs mal damit:

    update tab1
    set    feld = 1
    where  id in (select t1.id
                  from   (select t2.id
                          from   tab2 t2
                          where  id2 = 123) t1)

    der code ist zwar nicht getestet, aber die idee dürfte klar sein.
    hoffe du kommst damit weiter. ;)

    greets
    der kleine auf der linken schulter