PHP MySQL function to update records slowly

[monkeytooth]

I want to know is there a method to slow down the process of updating records. I know this doesn't sound like an Ideal thing to do, however. I have 20,000+ records I need to devise something to slow down the process of running through the records to do the updates I need done. I have crashed my hosting providers SQL server 2 times already in the last 24 hours attempting to do the process normally. It gets through about 12,000 of the records and then screeches to a halt, resulting in a server 500 or 501 error or something like that. My hosting provider is superbly pissed at me right now to say the least for my efforts. Fortunately I Was able to talk them out of booting me from the system.

 

So is there a method I can crawl a database table with using php that can go through the records slowly doing what i need done, or what would anyone suggest doing in a scenario like this? Also I should mention the records are constantly growing last week they were at 12,000. Im up for any suggestions on this if there are any to give. Also if youve done something like this could you provide a sample of concept?

[Alex]

Why don't you break it up and add a sleep() for a few seconds somewhere in between the queries?

[monkeytooth]

I actually tried that.. I hit about 14,500 before it crashed the second time..

 

I am attempting to update the records through a loop.. I have the loop count in a var to 200, if that var hits 200, then it sleeps for 5 seconds, and continues. But I think through typing this out I may have figured out my potential problem. But if that doesnt solve it then I am fresh out of ideas. Im going to attempt that real quick see how that works.

[Alex]

Try something like:

 

Say you have this update query that will update 20,000 rows:

 

mysql_query("UPDATE `table` SET row='value'");

 

You could do:

 

mysql_query("UPDATE `table` SET row='value' ORDER BY id LIMIT 9999");
sleep(5);
mysql_query("UPDATE `table` SET row='value' ORDER BY id LIMIT 10000, 10000");

[monkeytooth]

That can definitely work for a couple of the tasks I have, more so as I didnt think about limiting it like that through the query itself. Though one of the tasks I have is the key problem. I am sorting through, confirming a particular columns string value. Making sure that it falls within the constraints that it should. As they were user input based at one point and the developer I had working on it prior didn't think to confirm it upon entry. To ensure its validity. Now I'm going through picking up the mess so to speak so the clients site works accordingly.

 

So that particular part to my extent of knowledge I have to run through a loop of some form cycling all the records through a function to ensure the string is valid to the constraints we need it to be.

 

That aside, I must thank you for that last bit you threw up here.. that would have evaded me all together but such is the simplest things, always slipping by when trying to think more complexly about solving an issue that can be easily fixed otherwise.