ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
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.
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.
UPDATE users SET status='xxx' WHERE ( user='tux' or user = 'abc' or user = 'def')
...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.
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..
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.
Code:
update m.amt = t.amt
from
workingtable m, temptable t
where
m.username = t.username
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.