NYCPHP Meetup

[jon baer]

http://www.mysql.com/newsletter/2003-09/

MYSQL TIPS & HINTS

Using ORDER BY with UPDATE to resolve the
duplicate key problem

    Indrek Siitan

    To illustrate the problem, let us create a small
    sample table:
    
      CREATE TABLE items (
        id INT NOT NULL PRIMARY KEY,
        name VARCHAR(32)
      ); 
    
    Let's add a couple of rows as well:
    
      INSERT INTO items VALUES (1,'bar'),(2,'qux');
    
    Now, we want to add an entry named "foo" with an
    ID of 1, and shift all of the existing items up by
    one. Easy, you say - just add 1 to the id field
    values and then insert the new row:
    
      UPDATE items SET id=id+1;
      INSERT INTO items VALUES (1,'foo');
    
    In theory, everything looks fine. In real life,
    however, the UPDATE query most probably bails out
    with the following error:
    
      ERROR 1062: Duplicate entry '2' for key 1
    
    This happens because key constraints are checked
    before updating each row (with MyISAM tables that
    are non-transactional, there's no other way, and
    for some reason InnoDB follows the same path). The
    update is done in the order the rows are stored in
    the table files, which, in case you haven't mixed
    DELETEs and INSERTs and caused the table to get
    fragmented, is the order you have inserted the
    rows into the table. So if MySQL starts to process
    the first row and tries to increment the id field
    by 1, the result of 2 already exists in the table
    and produces the error above, although it would be
    increased next and the final query result would
    not violate the key uniqueness.
    
    But not to worry, MySQL has extended the UDPATE
    syntax with a possible ORDER BY clause, that will
    help us solve this problem. If we change the
    update query to:
    
    UPDATE items SET id=id+1 ORDER BY id DESC;
    
    With this ORDER BY clause, MySQL processes rows in
    descending order of id number: First, it updates 2
    to 3, and then 1 to 2.  Consequently, no
    duplicate-key violation occurs and the statement
    succeeds.
    
    The ORDER BY clause in UPDATE is available in
    MySQL server version 4.0.0 and newer.

Is row locking really necessary in multi-table
updates?

    Is row locking really necessary in a multi-table
    update, where some of the tables are only read
    during the update. Couldn't you speed up the
    updates, if the row locking would not be used?
    
    Consider the example below:
    ....
    mysql> create table table1(a int, b int) type =
    innodb;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> create table table2(a int, b int) type =
    innodb;
    Query OK, 0 rows affected (0.50 sec)
    
    mysql> insert into table1 values (10, 20);
    Query OK, 1 row affected (2.68 sec)
    
    mysql> insert into table2 values (10, 20);
    Query OK, 1 row affected (2.76 sec)
    
    mysql> update table1, table2 set table1.b = 100
    where table1.a = 
    table2.a;
    Query OK, 1 row affected (48.31 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    ....
    
    If we do not lock rows in table2, then the changes
    to table1 are based on an old consistent snapshot
    of table2. If that snapshot is very old, we may
    get really unexpected update results. So it is not
    good idea to set a trap to users by removing the
    row locks on table2.
    
    When you are doing multi-table updates, you might
    run into deadlocks.
    Deadlocks are a problem that needs to be taken
    into account, when using row locking and
    transactions. The link
    http://www.innodb.com/ibman.html#Cope_with_deadlocks
    contains some help in avoiding them.

TIP: Maximum size of a BLOB

    MySQL supports BLOBs (Binary Large Objects), which
    means you can store any binary file into MySQL.
    Many people ask, what is the maximum size of a
    BLOB in MySQL.
    
    The theoretical limit in MySQL 4.0 is 2G, however
    each blob requires generally to have 3 copies of
    it in the memory (stored in various buffers) so
    you need a lot of memory, if you have large BLOBs
    stored in MySQL. This is the reason, why the
    theoretical limit can be reached only on 64bit
    systems. The Practical limits are around some
    hundreds of megs per BLOB.

pgp key: http://www.jonbaer.net/jonbaer.asc
fingerprint: F438 A47E C45E 8B27 F68C 1F9B 41DB DB8B 9A0C AF47