CODEWITHSUNDEEP
mysqldatabase-performance
Tim
Tim

Reputation: 273

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';

Upvotes: 0

Views: 365

Answers (2)

Wes H
Wes H

Reputation: 4439

How long does it take to select the record, without the update?

If select is fast then you need to look into things that can affect update/write speed.

  • too many indexes on the table, don't forget filtered indexes and indexed views
  • the index pages have 0 fill factor and need to split to accommodate the data change.
  • referential constraints with cascade
  • triggers
  • slow write speed at the storage level

If the select is slow

  • old/bad statistics on the index
  • extreme fragmentation
  • columnstore index with too many open rowgroups

If the select speed improves significantly after the first time, you may be having some cold buffer performance issues. That could point to storage I/O problems as well.

You may also be having concurrency issues caused by another process locking the table momentarily.

Finally, any chance the tool executing the query is returning a false duration? For example, SQL Server Management Studio can occasionally be slow to return a large resultset, even if the server handled it very quickly.

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

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

Upvotes: 1

Related Questions