On 23/11/11 15:16, Carl (CBM) wrote:
One more DB performance report/question. I am seeing some UPDATE queries that only change one row but take much, much longer than they ought to. Is anyone else seeing this?
For example, the following type of query is getting killed from time to time in the p_enwp10 database on sql-s1. The query killers says it ran for over 400 seconds before being killed. The update is on a primary key, and I don't see any way to optimize it. At the time this is running the database connection is inside a transaction (AutoCommit = 0) if that matters.
UPDATE tmpcategories SET c_category = 'A-Class_Water_supply_and_sanitation_articles', c_ranking = '425', c_replacement = 'A-Class' WHERE c_project = 'Water_supply_and_sanitation' and c_rating= 'A-Class' and c_type = 'quality'
- Carl
I guess you could make the query sorter by splitting it adding a LIMIT clause, then rexecing while the affected rows = limit. (Assuming you don't need the UPDATE to be atomic)