Really slow UPDATE IGNORE with innodb

Discussion in 'Databases' started by juicytuna, Sep 25, 2007.

0
  1. #1
    Hi,

    I have a table with the following structure;

    CREATE TABLE my_table
    (
    id_1 int(11) NOT NULL ,
    id_2 int(10) NOT NULL ,
    stauts tinyint(1) NOT NULL DEFAULT 0 ,
    PRIMARY KEY (id_1)
    ) Engine =InnoDB';


    The table currently has arround 100,000 entries. When I try to run variations of the following statement it is taking around 4 seconds per query;

    UPDATE IGNORE my_table
    SET id_1 = 74240, id_2 = 5


    I need it to be running a lot faster than 4 seconds per query as I need to update upwards of 100,000 records a day! My server is fairly beefy, a 3 gig dual core opeteron and is generaly running below 1.0 load.

    Where am I going wrong?

    Thanks.
     
    juicytuna, Sep 25, 2007 IP
  2. kjewat

    kjewat Active Member

    Messages:
    149
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    53
    #2
    Does it make sense to set the primary key id_1 to 74240 for all rows ?

    Shouldn't the primary key in general uniquely identify a row?
    In fact, I think the primary key must be unique...

    Do you mean to update the row where id_1 = 74240 and set id_2=5 ??
    In that case the query should be:
    UPDATE my_table
    SET id_2 = 5 WHERE id_1 = 74240

    I see you have the IGNORE keyword in your query, so any errors will be ignored and the query will continue. But I don't know if the errors that occur because of the duplicate primary keys still leads to longer querying time.
     
    kjewat, Sep 25, 2007 IP