Search code examples
mysqldatabase-performance

Slow time updating primary key indexed row


I have a query that updates a field in a table using the primary key to locate the row. The table can contain many rows where the date/time field is initially NULL, and then is updated with a date/time stamp using NOW().

When I run the update statement on the table, I am getting a slow query log entry (3.38 seconds). The log indicates that 200,000 rows were examined. Why would that many rows be examined if I am using the PK to identify the row being updated?

Primary key is item_id and customer_id. I have verified the PRIMARY key is correct in the mySQL table structure.

UPDATE cust_item
   SET status = 'approved',
       lstupd_dtm = NOW()
 WHERE customer_id = '7301'
   AND item_id = '12498';

Solution

  • I wonder if it's a hardware issue.

    While the changes I've mentioned in comments might help slightly, in truth, I cannot replicate this issue...

    I have a data set of roughly 1m rows...:

    CREATE TABLE cust_item
    (customer_id INT NOT NULL
    ,item_id INT NOT NULL
    ,status VARCHAR(12) NULL
    ,PRIMARY KEY(customer_id,item_id)
    );
    
    -- INSERT some random rows...
    
    SELECT COUNT(*)
         , SUM(customer_id = 358) dense
         , SUM(item_id=12498) sparse 
    FROM cust_item;
    +----------+-------+--------+
    | COUNT(*) | dense | sparse |
    +----------+-------+--------+
    |  1047720 |   104 |      8 |
    +----------+-------+--------+
    
    UPDATE cust_item 
       SET status = 'approved' 
     WHERE item_id = '12498' 
       AND customer_id = '358';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0