Forum Stats

  • 3,874,172 Users
  • 2,266,676 Discussions
  • 7,911,751 Comments

Discussions

update with pk in subquery runs too long

Hello out there,

I have an update like this

update dt_mabo_erf_seite s set
   bearbeitungsdatum = sysdate()
where
   s.bearbeitungsdatum is null and
   s.mabo_erf_seite_id in
   (select
       b.mabo_erf_seite_id
    from
       dt_mabo_erf_block b
    where
       b.webbild_id = 42);

This runs way too long on MySQL 5.7. MySQL Workbench shows this execution plan:

Now the question is, how can I change this that the update first gets the IDs to update and then does the update just for these instead of scanning the whole index.

mabo_erf_seite_id is the primary key for dt_mabo_erf_seite and foreign key on dt_mabo_erf_block.

I'm used to Oracle SQL and there it runs the way that I expect.

Best Answer

  • Lothar Armbrüster
    Lothar Armbrüster Member Posts: 44 Bronze Badge

    Found out myself:

    update
       dt_mabo_erf_seite s join dt_mabo_erf_block b
       on b.mabo_erf_seite_id=s.mabo_erf_seite_id set
       bearbeitungsdatum = sysdate()
    where
       s.bearbeitungsdatum is null and
       b.webbild_id = 42;
    

    That gives this execution plan:


Answers

  • Lothar Armbrüster
    Lothar Armbrüster Member Posts: 44 Bronze Badge

    Found out myself:

    update
       dt_mabo_erf_seite s join dt_mabo_erf_block b
       on b.mabo_erf_seite_id=s.mabo_erf_seite_id set
       bearbeitungsdatum = sysdate()
    where
       s.bearbeitungsdatum is null and
       b.webbild_id = 42;
    

    That gives this execution plan: