Does anyone know what will be more efficient and use less resources:
Method 1 . Using a single SELECT statement to retrieve data from one table and then iterate through it to perform multiple UPDATEs in another table. EG. (pseudo code, execute () starts the request):
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 one INSERT .. ON DUPLICATE KEY UPDATE statement with a nested SELECT statement. For instance:.
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. The identifier on tableB is the primary key. In fact, there will be no INSERT, because I know that the key will exist. So all this is UPDATE. Just using this operator to pass in one request, not in several.
, . , , ? ?
-, ( ).
?