LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 04-09-2005, 09:31 PM   #1
macie
Member
 
Registered: Oct 2003
Location: /dev/null
Posts: 33

Rep: Reputation: 15
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.
 
Old 04-10-2005, 06:47 AM   #2
ahwkong
Member
 
Registered: Aug 2004
Location: Australia
Distribution: Fedora
Posts: 282

Rep: Reputation: 30
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.
 
Old 04-10-2005, 09:18 AM   #3
Proud
Senior Member
 
Registered: Dec 2002
Location: England
Distribution: Used to use Mandrake/Mandriva
Posts: 2,794

Rep: Reputation: 116Reputation: 116
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.
 
Old 04-10-2005, 03:20 PM   #4
macie
Member
 
Registered: Oct 2003
Location: /dev/null
Posts: 33

Original Poster
Rep: Reputation: 15
something like...
Code:
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.

any recommendations?
 
Old 04-10-2005, 03:51 PM   #5
Proud
Senior Member
 
Registered: Dec 2002
Location: England
Distribution: Used to use Mandrake/Mandriva
Posts: 2,794

Rep: Reputation: 116Reputation: 116
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.
 
Old 04-11-2005, 01:38 AM   #6
ahwkong
Member
 
Registered: Aug 2004
Location: Australia
Distribution: Fedora
Posts: 282

Rep: Reputation: 30
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?
 
Old 04-14-2005, 12:14 AM   #7
macie
Member
 
Registered: Oct 2003
Location: /dev/null
Posts: 33

Original Poster
Rep: Reputation: 15
...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?

Last edited by macie; 04-14-2005 at 12:16 AM.
 
Old 04-14-2005, 12:45 AM   #8
ahwkong
Member
 
Registered: Aug 2004
Location: Australia
Distribution: Fedora
Posts: 282

Rep: Reputation: 30
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.

Code:
update m.amt = t.amt
from
     workingtable m, temptable t
where 
    m.username = t.username

Last edited by ahwkong; 04-14-2005 at 12:46 AM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
IP Table help, Droping entire 200 block of ip's? Wide glide Linux - Security 2 12-03-2004 01:39 AM
mySQL table update DropHit Linux - Software 5 02-04-2004 11:47 PM
MySQL non-realtime table-by-table mirroring Passive Linux - Software 1 01-20-2004 01:11 PM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 12:30 PM
Dates and Times in MySQL oulevon General 2 09-13-2001 04:27 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 08:41 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration