mysql performance: nested insert/duplicate key vs multiple updates

Question

Hello. Does anyone know what would be more efficient and use less resources:

Method 1-- Using a single SELECT statement to get data from one table and then iterating through it to execute multiple UPDATEs on another table. E.G. (pseudo-code, execute() runs query):

Query1_resultset = execute("SELECT item_id, sum(views) as view_count FROM tableA WHERE condition=1");  
while(Query1_resultset as row) {  
   execute("UPDATE tableB SET view_count=row.view_count WHERE id=row.item_id");  
}

Method 2-- Use a single INSERT.. ON DUPLICATE KEY UPDATE statement with a nested SELECT statement. E.G.:

INSERT INTO tableB (id, view_count) SELECT item_id, SUM(views) as view_count FROM tableA WHERE condition=1 ON DUPLICATE KEY UPDATE view_count=VALUES(view_count);

Note: ID on tableB is a primary key. There actually won't be any INSERTS because I know the key will exist. So it's all UPDATEs. Just using this statement to pass in a single query rather than multiple.

I'm really curious as to why either would be more efficient. Is it the number of queries that determines how quickly it will run? Where is the bottleneck? I'm looking for something that will scale (the number of rows being updated grows daily).

Any ideas?

Thanks

Answer 1

I think INSERT.. ON DUPLICATE KEY UPDATE is more efficient (otherwise, it wouldn't make much sense to add such an extension). By the way, your first example is not exactly the same as the second one - you neither use transactions nor you lock the table, so it's possible that the record returned by SELECT will not exist by the time you execute UPDATE.

Answer 2

It depens on your update/insert ratio. If you have lots of inserts and only a couple of updates than the INSERT ... ON DUPLICATE KEY UPDATE statement will be faster.

If you mainly have updates, than you would be better off with an UPDATE statement and an insert as fallback (if there was no update). You could use the multi table update clause to do it with a single update instead of a select followed by an update by the way. If you're doing both a SELECT and an UPDATE than the INSERT will definately be faster.