Way to update mysql table 200+ times at once
 

What would be the fastest and most efficient way to update a mysql table 200 or more times (at the same instance) without bringing much strain on the database and the webserver. For example, I need to query something similar to this: "UPDATE users SET status='xxx' WHERE user='tux';" ...for about 200 different users with each 'status' filed being different for each user. I know a simple loop can take care of that (using PHP btw) but that would initialize a single query for each user while what I would prefer is a single query to update all users... thx.

How about using 'or'?

i.e.

UPDATE users SET status='xxx' WHERE ( user='tux' or user = 'abc' or user = 'def')

Certainly you may not/cannot put all 200 logins in one single sql stmt. So, you can build and submit one such SQL for, says, every 50 users.

Well if you're saying you cant do a simple UPDATE users SET status='xxx' WHERE users=*; because you want a different status for each user, than you'll have to loop it and create individual queries.

Is there no pattern to the usernames or order of the records that would allow a few pattern matching/number limited queries instead?

Is there evidence that 200 unique queries would be slower than some single query-you still want the same result of changing however many rows so the same amount of work would have to be done either way.

something like...
...won't work since 'status' is different for each user.

There also are no patterns I can match it in to reduce the queries, all fields are unique. As far as putting it all in a loop is probably the easiest way, but 200+ queries (and it will only get more) each being sent as an idividual query rather then a single query does reduce the speed of the update and over all performance, espcially since it's a very busy site.

any recommendations?

How about using Stored Procedures as then it's in the database and you simply call it-also keeps code down and uniform operation independant of the calling source. :)

I am sorry I missed the point that 'status' is different for each individual user.

But it 'troubles' me a little bit. More often than not 'status' is used to refer to a set of 'flags' that are ususally finite in number and indicative of some particular states associcated with either the an individual object or the overall application.

A good example is the ebay buyer status. A buyer may be of one of these status:

1) Placed a bid
2) Won an auction
3) Made the payment
4) Recived the shippment
5) Placed a feedback

And you can rather instantly make out the relationship between these status.

Back to your issue: If each user has a status of its own, then, unlike the example I just cited, the characteristic of this 'status' is more akin to a 'ticket', right?

Then may I suggest that, instead of try to tackle the solution via technical means, is there any possibility to simplify the algorithm?

e.g. if the 'status' actually behaves more or less like a priority number (which is used to order the users in a certain way) then it makes more sense to use a stored procedure to generate these 'status' in the server side, instead of firing 100s sql stmt?

Do you think you can share with us the meaning of this 'status' and/or some example of these status?

...sorry for not explaining the query in more detail. The 'status' filed is actually a numeric filed which indicates the $ amount currently due on the account. So basically, each user has their own bill (pretty much unique for each user) The whole purpose of this is to be able to update each month's billing information based on the table from the prior month.... So if there are 200 users for this month, I need to issue 200x "UPDATE users SET status='$amount_due' WHERE user='$username';" so all information can be updated for each user... and yes, it all has to happen at once. This goes in to much more detail actually, which i really don't want to go in to, as well as the reasons why it has to be updated in such way... nonetheless, the type of update to the mysql table i need has been done before I just don't remember how... so I'm stuck at this..

any suggestions?

OK. Your top 'priority' is:

You want to have the update applied to 200+ people all at once.


I think there are two solutions:

1) as someone else has suggested: put the business logic in the server as a stored proc. It has an added advantage: It can scheduled to run at night as a batch job. No data is only available in php side, right?

2) use a temporary table, which has at least two fields: username and the billamount. Fire those 200+ query but update to this temp table. At the end, issue one single join and update sql statement to update the billamount of the working table based on the data in the temp table. Truncate the temp table after done.