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