1000-10000 mysql queries in one page - will server break?

[notset]

Hello,

there is one PHP file (cronjob.php), which will update mysql database every 24 hours.

Script will always do 6 required sql queries and 3 additional queries for every user in the database. But what if my site has 1000 or even 10000 users? Will server break? Queries data contains only number, no long text or etc.

So, if 3-30k mysql queries for server at one time is too much, what to do then?

Thank's for comments.

[Roger_Ramjet]

Rewrite the queries to update more than one user at a time - like all of them at once the way it should be done in sql.

[notset]

I don't know how is possible to do this in one query without a cycle.

$sql = "SELECT `id`,`amount`,`roi` FROM `inv` WHERE `status` = 1";
$result = mysql_query($sql) OR db_error(mysql_error());

while($deposit = mysql_fetch_assoc($result)) {
	if ($deposit['roi'] + $roi > TOTAL_ROI) {
		$return = ((TOTAL_ROI - $deposit['roi']) * $deposit['amount']) / 100;
		$sql = "UPDATE `inv` SET
			`roi` = ".TOTAL_ROI.",
			`status` = 0
			WHERE `id` = ".$deposit['id']
		;
		mysql_query($sql) OR db_error(mysql_error());
	}
	else {
		$return = ($roi * $deposit['amount']) / 100;
		$sql = "UPDATE `inv` SET
			`roi` = `roi` + ".$roi."
			WHERE `id` = ".$deposit['id']
		;
		mysql_query($sql) OR db_error(mysql_error());
	}
}

[Roger_Ramjet]

It would help if you posted all the code. Where the heck does $roi come from? It certainly does not comes from the first query. and TOTAL_ROI for that matter.

[Installer]

Keeping in mind what Roger said, and also that $return seems to be unused completely, it seems to me you could do it in two queries (no SELECT) along these lines:

$sql = "UPDATE inv
        SET roi = " . TOTAL_ROI . ", status = 0
        WHERE status = 1 AND roi + $roi > " . TOTAL_ROI;
mysql_query($sql) or db_error(mysql_error()); 

$sql = "UPDATE inv
        SET roi = roi + $roi
        WHERE status = 1 AND roi + $roi <= " . TOTAL_ROI;
mysql_query($sql) or db_error(mysql_error());

Edit: Actually, since in the second query the remaining status = 1 rows will all meet the second WHERE conditon, you could leave that condition out to speed things up even more.

[notset]

$return is used in the next step...
Ok, here is all code.

$sql = "SELECT SUM(`amount`) FROM `deposits` WHERE `status` = 1";
$deposits = db_get($sql);

$sql = "SELECT `amount` FROM `funds` WHERE `day` = 1";
$funds = db_get($sql);

$roi = ($funds / $deposits) * 100;

$paid = 0;

$sql = "SELECT `id`,`uid`,`amount`,`roi` FROM `deposits` WHERE `status` = 1";
$result = mysql_query($sql) OR db_error(mysql_error());

while($deposit = mysql_fetch_assoc($result)) {
	if ($deposit['roi'] + $roi > TOTAL_ROI) {
		$return = ((TOTAL_ROI - $deposit['roi']) * $deposit['amount']) / 100;
		$sql = "UPDATE `deposits` SET
			`roi` = ".TOTAL_ROI.",
			`last_update` = UNIX_TIMESTAMP(),
			`status` = 0
			WHERE `id` = ".$deposit['id']
		;
		mysql_query($sql) OR db_error(mysql_error());
	}
	else {
		$return = ($roi * $deposit['amount']) / 100;
		$sql = "UPDATE `deposits` SET
			`roi` = `roi` + ".$roi.",
			`last_update` = UNIX_TIMESTAMP(),
			WHERE `id` = ".$deposit['id']
		;
		mysql_query($sql) OR db_error(mysql_error());
	}
	$sql = "UPDATE `users` SET `balance` = `balance` + ".$return." WHERE `uid` = ".$deposit['uid'];
	mysql_query($sql) OR db_error(mysql_error());
	$sql = "INSERT INTO `history` SET
		`type` = 2,
		`uid` = ".$deposit['uid'].",
		`amount` = ".$return.",
		`description` = ".$deposit['id'].",
		`time` = UNIX_TIMESTAMP()
	";
	mysql_query($sql) OR db_error(mysql_error());
	$paid += $return;
}

$sql = "UPDATE `funds` SET `amount` = `amount` - ".$paid." WHERE `day` = 1";
mysql_query($sql) OR db_error(mysql_error());

[Installer]

But with the code you posted, $result will have only the value assigned in the last iteration of your while loop.

I assume you're testing your code with a smaller test table.

Edit: OK, now I see what you do with $return. How about adding another column to the table to store $return in, then sum it with a third query (or sum it from the return table)?

Edit again: And after taking a closer look at your more complete code, I see you want to do a lot more than what you said in your first code post.

[Roger_Ramjet]

OK, what you have to do is to get away from procedural programming paradigm and into database paradigm. You are working through each account completely before moving on to the next, that is the procedural way. What you have to do is to work through each step in the calculations for all records using sql, then you will just be running 5-10 queries.

Now, I'm not going to try and get my head around the details of these complex calculations so I'll have to explain this in a more genric way.

I have a calc-debt function in an access app that does a similar thing:
accounts have a starting balance, they acrue fees, payments, credits and debits to arrive at a current balance. So,
query 1 calculates total fees and stores that in a column in the debt
2 cals total credits
3 calcs total debits
4 calcs total payments
5 then calculates the balance from those stored totals
6 then runs an audit trail

100,000 accounts in 6 queries

The thing that is throwing you is the conditional processing if ($deposit['roi'] + $roi > TOTAL_ROI) {
You can use IF within a query to handle this Control Flow Functions , or just run 2 or 3 conditional queries as Installer suggests.

It is at times like this that you need to step back from your code and abstract the logic using pen and paper to identify each transformation step and then code the queries to perform each step.

[notset]

Thank you very much 🙂

[notset]

So i need to use transactions?

Maybe somebody could give me an example how to use mysql transactions with IF ?