09.Feb.2012

How to do a multi-table update with a limit in MySQL

According to the MySQL documentation, you can’t do a multi-table UPDATE with a LIMIT.

What’s a multi-table update with a limit? Well, something like this:

UPDATE
    foo
    , bar
SET
    foo.baz=bar.baz
WHERE
    foo.baz IS NULL
    AND foo.id=bar.id
LIMIT
    1000
;

(Which doesn’t work. Of course, you can do single table UPDATEs with a LIMIT just fine)

Why would you even want to do this?

Well, anytime you have monster sized tables, and you don’t want to lock everybody else while you either read (from bar) or write (to foo). If you can put a limit on the update, you can call it repeatedly, in small chunks, and not choke everything for all other users.

For example, if bar happens to have, say, ohhh, 30 million rows in it, foo happens to have ooh, 2 million rows and they’re both used by everything, all the time.

So, here’s a sneaky way to get around this limitation. I did promise one, right there in the title, after all.

UPDATE
    foo
    , (SELECT
            bar.id
            , bar.baz
        FROM
            foo
            , bar
        WHERE
            foo.id=bar.id
            AND foo.baz IS NULL
        LIMIT
            1000
    ) tmp
SET
    foo.baz=tmp.baz
WHERE
    foo.id=tmp.id
;

Some important notes:

  • The update conditions (foo.baz IS NULL) go inside the subquery, along with the LIMIT.
  • We have to match ids twice – once for the subquery, and once against the created temporary table. That’s why we make sure we SELECT both the id and baz from bar in the subquery.
  • There’s no conditionals (other than id match) on the outside WHERE condition, since we’ve done them all in the subquery.
  • MySQL also has a limitation of not allowing you to UPDATE while SELECTing from the same table in a subquery. Notice that this sneakily avoids it by only SELECTing from the other table.

So, how about that? You can now do limited multi-table updates.

Oh, except for one. Minor. Problem.

This doesn’t work with temp tables (eg if foo was created with a CREATE TEMPORARY TABLE statement).

Bugger.

However, here’s a sneaky way around that limitation too.

First of all, give your temp table another column, in the example below “ref_no INT”.

Make sure you have an index on the id, otherwise it’ll be dog slow.

Then do this:

# do this in chunks of 1k
SET @counter = 1;

REPEAT

    UPDATE
        tmp_foo
    SET
        ref_no=@counter
    WHERE
        ref_no=0
        AND baz IS NULL
    LIMIT
        1000
    ;
    COMMIT;

    UPDATE
        tmp_foo
        , bar
    SET
        tmp_foo.baz=bar.baz
    WHERE
        tmp_foo.ref_no=@counter
        AND tmp_foo.id=bar.id
    ;
    COMMIT;

    SET @counter = @counter + 1;

UNTIL (SELECT COUNT(id) FROM tmp_foo WHERE ref_no=0 AND baz IS NULL) = 0
END REPEAT;

Some important notes:

  • We’re basically flagging a thousand rows at a time, then matching only against those rows – pretty simple concept really.
  • The commits are in there because MySQL can be a bit weird about not propagating changes to the database if you don’t commit inside your stored proc. This ensures that updates are passed out, which also means I can run multiple copies of this stored proc concurrently with moderate safety (if I replace @counter with a suitably large RAND() value) – well, as much as you can normally expect with MySQL anyway.
  • If you want to reuse the temp table (say, to update something else from – a reverse update to that shown above) you’ll need to reset all the ref_no’s to 0.
  • Whatever conditions are in the initial WHERE need to be mirrored in the final SELECT COUNT.
  • Obviously just drop the table when you’re finished.

As a bonus, I’ve found this is actually quicker than doing one single large scale update. Why? Less memory is used.

So look at that. TWO ways to get multi-table updates with a limit. Nifty.

related

  • No Related Posts

09.Feb.2012

How to automatically position PuTTY terminal

PuTTY is arguably the best Windows terminal app. I’ve used it for years.

One thing that has always felt missing is the ability to automatically position it on the screen on startup. For example, I always have a couple of windows where I watch certain processes. They’re always in the same position on my screen, and always running the same scripts inside them. How do I do that?

So, you can imagine my glee when I found a patch out there that will do exactly that. Props go to Brad Goodman who wrote it. Follow that link to get both an exe (his patch applied to a 2005 build) and the source so you can patch it yourself. I’ve also put a copy of the exe here, in case his site disappears.

How to use it? Very simple.

  1. Save your session settings from inside PuTTY, as normal
  2. Start up putty-bkg.exe (instead of the usual putty.exe)
  3. Now when you load your sessions you’ll also see the new “Position & Icon” options, under “Window”:

    Enter the desired top, left (& icon, if you feel inspired)

  4. Alter them to your heart’s content (don’t forget to also adjust the rows & columns under “Window” to get it the right size too), save your session and voila.

Even more useful is if you combine this with setting a remote command to be run at initial connection.

  1. Click on “SSH” under “Connection” (you can’t do this if the session is already active)
  2. Enter the command or script you want run in the “Remote Command” textbox:

    Enter the startup command

  3. Click the “Save” button (under “Session”) as usual.

Now, to run all of the above, just setup a shortcut to:

“C:\Program Files\putty\putty-bkg.exe” -load [session name]

(where [session name] is whatever you saved your session as). Obviously adjust the path to wherever you put putty.bkg.exe.

Next time you click the shortcut, not only will PuTTY be positioned correctly on the screen, but it’ll also automatically be running whatever script you desire. Just like magic! (but with less jiggery pokery)

related

  • No Related Posts

Mastodon