mysql -- most efficient way to batch process UPDATES

drewbee · 05-05-2008, 06:06 PM

Hey all. I currently have a program that can be launched by the user of which does processing on their records specific. Part of the process is to select out specific records and make an update to them with the recently pulled ID.

Currently I am running the program to batch update in groups of 25. I will select 25 records that need updated, and once they are all pulled in, update each specific row with its new data.

The problem this poses is that I do not want to try and do this as efficiently as possible (since it is an unknown as to how many users may launch this at anyone time).

Doing a UPDATE query call for each iteration is unacceptable (I think anyways).

and for some reason, mysqli_multi_query took almost twice the amount of time as an acutal individual call to each update.

Does anyone know the basics behind how transactions work? If i loop over the updates within a transaction, then call a commit at the end does this force it to truely be done within 1 call? or does will it still make 25 seperate calls to the database for the update?

Psuedo Code:

PHP Code:


			
{start transaction}
  {foreach $record}
    {UPDATE table set foo = bar WHERE id = $record[id]}
  {/foreach}
{/end transaction; commit}

Is this the best possible way to tackle this?

SOCK · 05-05-2008, 06:20 PM

Can you give us the specifics of the query itself? In other words, is it something like your example where you're setting the same value for every record that match on a set of record ID values?

If that's the case, then use a single query referencing a list of ID values using the IN operator, e.g.

Code:

UPDATE yourTable
SET column1 = 'new value'
WHERE id IN (3,5,7,9,11,13);

A transaction is an interesting idea; what engine type are you working with? MyISAM or InnoDB?

drewbee · 05-05-2008, 06:25 PM

InnoDB -- I need the huge maximum record count as well as row level locking for speed.

Right ~ These updates are unique values to each row pulled from another source, so the above will not work.

Each row will be updated based on the WHERE clause to single it down... however the values being updated are completely irrelevant from one row to another.

drewbee · 05-06-2008, 12:09 PM

Anyone have any insight or tips into this? I tried searching around, but am not getting a very definative answer on how transactions are processed with multiple queries. I know there will be 25 seperate queries fired off... however I am curious as to whether or not it sends the query with each request, or will wait for the final commit to be made. Obviously before the commit happens, the changes wont happen yet... however I think they go to some type of staging area in the table with each request? Am I way off on this one?