Help Joining These Queries (A Cron Job That Is Destroying My Site)

[acidpunk]

Hello everyone, back for some advice, well really help. I'm running a cron job that is basically lagging my entire site. I have 10 queries, that are all running through about 600,00 rows. I'm in some need of help. I've read about joining queries but i'm really stuck. Could someone help me optimize this page to help me save my site?

 

$userQuery = mysql_query("SELECT id,jewel_attack,jewel_hp,jewel_eph,jewel_critical,jewel_aph,jewel_max,jewel_cap FROM users");
while ($user = mysql_fetch_array($userQuery,MYSQL_ASSOC))
{

$userid = $user['id'];


$item = mysql_fetch_array(mysql_query("select attack,hp,critical,attacks_per_hour,max_attacks,attack_cap from user_equipment where equipped='Yes' and owner='$userid' and slot='Weapon'"));

$item11 = mysql_fetch_array(mysql_query("select attack,hp,critical,attacks_per_hour,max_attacks,attack_cap from user_equipment where equipped='Yes' and owner='$userid' and slot='Body'"));

$item22 = mysql_fetch_array(mysql_query("select attack,hp,critical,attacks_per_hour,max_attacks,attack_cap from user_equipment where equipped='Yes' and owner='$userid' and slot='Shield'"));

$item33 = mysql_fetch_array(mysql_query("select attack,hp,critical,attacks_per_hour,max_attacks,attack_cap from user_equipment where equipped='Yes' and owner='$userid' and slot='Head'"));

$item4 = mysql_fetch_array(mysql_query("select attack,hp,critical,attacks_per_hour,max_attacks,attack_cap from user_equipment where equipped='Yes' and owner='$userid' and slot='Belt'"));

$item5 = mysql_fetch_array(mysql_query("select attack,hp,critical,attacks_per_hour,max_attacks,attack_cap from user_equipment where equipped='Yes' and owner='$userid' and slot='Foot'"));

$item6 = mysql_fetch_array(mysql_query("select attack,hp,critical,attacks_per_hour,max_attacks,attack_cap from user_equipment where equipped='Yes' and owner='$userid' and slot='Pants'"));

$item7 = mysql_fetch_array(mysql_query("select attack,hp,critical,attacks_per_hour,max_attacks,attack_cap from user_equipment where equipped='Yes' and owner='$userid' and slot='Ring'"));

$item8 = mysql_fetch_array(mysql_query("select attack,hp,critical,attacks_per_hour,max_attacks,attack_cap from user_equipment where equipped='Yes' and owner='$userid' and slot='Neck'"));

$itemorb1 = mysql_fetch_array(mysql_query("select attack,hp,critical,attacks_per_hour,max_attacks,attack_cap from user_equipment where equipped='Yes' and owner='$userid' and slot='Orb1'"));

$itemorb2 = mysql_fetch_array(mysql_query("select attack,hp,critical,attacks_per_hour,max_attacks,attack_cap from user_equipment where equipped='Yes' and owner='$userid' and slot='Orb2'"));

$itemorb3 = mysql_fetch_array(mysql_query("select attack,hp,critical,attacks_per_hour,max_attacks,attack_cap from user_equipment where equipped='Yes' and owner='$userid' and slot='Orb3'"));

$level = mysql_query("select * from level");
while ($lvl = mysql_fetch_array($level))
{

 

next comes adding everything together. $item1['attacks']+$item2['attacks'] ect ect ect. for everything 'attack','critical', ect (everything inside the select in the user_equipment selects.

 

This page will lag my entire site (well it is) i'm running nginx, site used to have load times of .001 seconds, now some pages load in 14 seconds. But this page, is running 4 CPU'S at 100% every 5 minutes (since thats when its time to load) if someone can help me, i'd really love it.

 

Optimizing is rough lol.

[Jessica]

WHY are you doing so many queries!!!?!?!?

 

Also, use a SUM().

[acidpunk]

Lol! thats what i'm saying. haha, which is what i'm asking for help for. Any examples on how to make this better. As of right now, this script is running 4 CPU'S 100%

 

I'm trying to get better at this

[acidpunk]

I see about the sum. Can i wrap everything inside 1 sum? like sum(attack,hp,critical) ect

[Jessica]

Yes

[acidpunk]

$item = mysql_fetch_array(mysql_query("select SUM(attack,hp,critical,attacks_per_hour,max_attacks,attack_cap) from user_equipment where equipped='Yes' and owner='$userid' and slot='Weapon'"));

$attack = $item['SUM(attack)'];
$hp = $item['SUM(hp)'];
$critical = $item['SUM(critical)'];
$aph = $item['SUM(attacks_per_hour)'];
$max = $item['SUM(max_attacks)'];
$cap = $item['SUM(attack_cap)'];


$item2 = mysql_fetch_array(mysql_query("select SUM(attack,hp,critical,attacks_per_hour,max_attacks,attack_cap) from user_equipment where equipped='Yes' and owner='$userid' and slot='Shield'"));


$attack2 = $item2['SUM(attack)'];
$hp2 = $item2['SUM(hp)'];
$critical2 = $item2['SUM(critical)'];
$aph2 = $item2['SUM(attacks_per_hour)'];
$max2 = $item2['SUM(max_attacks)'];
$cap2 = $item2['SUM(attack_cap)'];

$total_attack = $attack+attack2;

 

so this is what I got from what you said to do (i think this is how you meant anyways, i tried.)

 

(so now i have like 10 other queries.)

 

So now, how can the problem with these useless 10 queries. :/

Edited October 8, 2012 by acidpunk

[Jessica]

Why would you select each slot separately?

[acidpunk]

The only way i know how too. Which is what i'm trying to figure out, there has to be a better solution.

[acidpunk]

wait, wouldn't it work if i took out the AND slot='weapon' part, and just did

 

mysql_query("SELECT from user_equipment WHERE equipped=Yes and owner=$userid) lol?!

 

it would be basically doing the same exact thing, wouldn't it..

 

if i'm wrong tell me please, but i think that might be the solution here.

[Jessica]

If you look at the manual you will see examples of looping through result sets. You would select all the data you need at once

[acidpunk]

I love more puzzles.

[PFMaBiSmAd]

owner=$userid

 

^^^ You wouldn't even have the owner test in the WHERE clause, as that implies you are doing this all inside of a loop of $userid's.

[kicken]

It's not really clear what exactly you're doing with the code provided (as in, what results you really need, but I gather you are looking to get the sum of all the fields for each user, for each slot.

 

It's possible to write a single select query that would return a result set such as:

 

+-----------+-------------+------------------+

| userId | slot | sumOfEverything |

+-----------+-------------+------------------+

| 1 | Weapon | 12 |

| 1 | Body | 11 |

| 2 | Head | 9 |

| 5 | Belt | 20 |

....

+-----------+-------------+------------------+

 

Then you can use a few loops in your PHP code to process the data in whatever way necessary. Things you may need to read up on as far as how to write such a query would be JOIN's, and GROUP BY

 

If you can't figure it out still post the CREATE TABLE structures of your tables and describe what exactly you're trying to accomplish in this cron script.

[acidpunk]

Thank you for the replies.

 

Kitchen, yes your right i'm trying to gather all the information about the users 'items' he has, its an RPG game. Basically he has 9 slots (head, weapon, shield ect) And each slot has data like 'attack, hp, critical, ect' Now, what this script does is every 5 minutes check to users equipment, along with his level, and gives him the correct stats based off what equipment he has on + his level. The script i have is completely lagging my site down. I get stuck with a 'website is currently offline' error from cloudflare. I've had my server admin tell me its this script running 100% of 4 CPU'S every 5 minutes. Its a useless script basically and i'm not familiar enough with joining tables to create this script to get faster.I really appreciate the responses.

 

 

Lol, it sucks this 1 script can cause my whole site to fail.

[PFMaBiSmAd]

Without seeing all your existing logic, there's no way we can specifically help.

 

For example, you are selecting jewel_attack,jewel_hp,jewel_eph,jewel_critical,jewel_aph,jewel_max, and jewel_cap. How are you using those values? You are also selecting all the levels. How are you using those values?

[acidpunk]

The user table has jewel_attack,jewel_hp,jewel_eph. The level; has attack,hp (for their level) user_equipment has the values stored aswell. What do you want me to post (only reason i didnt post is usually people think i'm just expecting it to be remade) but that isn't the case, just need some guidance on how to optimize this the correct way.

 

if your willing to help, tell me what I need to post.

[acidpunk]

I'm really just adding everything together.

 

user jewel_atttack, jewel_hp, jewel_eph

 

say its attack

 

i'm adding equipment (weapon,shield, ect) + user jewel_attack + level_attack,

 

then i'd add all the HP'S together. Then the final resullt, update the users

[Psycho]

Stop querying all those fields if you don't need them. In the first query you are querying about 10 fields but only using the user ID field

 

Assuming the list of 'slots' you have in your otiginal queries are all th epossible slot values, the below should work for you. I assume you need a name or other informatin from the users table to associate with the data, but I didn't see anything in the original query. Just add those fields to the top line of the SELECT statement:

$query = "SELECT username,
	 SUM(attack) AS attack_sum, SUM(hp) AS hp_sum, SUM(critical) as critical_sum,
	 SUM(attacks_per_hour) as attack_per_hour_sum, SUM(max_attacks) AS max_attacks_sum,
	 SUM(attack_cap) as attack_cap_sum
 FROM users
 LEFT JOIN user_equipment
	 ON users.id = user_equipment.owner
 WHERE equipped='Yes'
 GROUP BY users.id";

Edited October 9, 2012 by Psycho

[acidpunk]

Psyco thank you! And ya, i am using all those fields, i just didnt post that part (its for when i add all them together)

 

so thats how i do a join query. I've been wondering how to do that for some time now, everything i ask how someone tells me to read about it on google lol. I'm going to try this, thank you so much. its weird, the site i'm running worked fine for over a month, and then all of a sudden it started to crash. I've been told its my queries. So i'm trying to fix all of them, and this page i posted is one of the culprits. Let me see if i can implement this.

 

I also realized I really did a terrible job providing information for anyone to help me. :/ lol

[Psycho]

If this is a cron job I suspect you are doing more than just SELECT queries. You should probabnly provide more details on what, exactly you are tryig to achieve. Depending on what you are really doing, this process may not even be needed. At least, it's usually not necessary to calculate sums within a cron job since that can be done in real time.

 

You also don't show what that last while loop is doing. If there are any queries in there, then you need to change that as well. Never, ever run queries in loops. In 99% of cases you can do it without looping queries. In the other 1% you're probably just doing something stupid :/

Edited October 9, 2012 by Psycho

[acidpunk]

Honestly what I did was took the whole cron job away.I realized it probably wasn't even needed, and was kind of a worthless script. What I did was add the necessary updates during the time the item was being equipped. So when it was being equipped, the stats +, and when it de-equipped the stats -

 

this brought my server back to life.! I guess case is solved

 

Thank you for the help everyone who tried, psycho thank you for actually trying to show me examples instead of telling me to go read about something lol.

 

My server isn't running 4 CPU'S anymore.

 

anyways for shits and giggles this is how worthless this script is.

 

$userQuery = mysql_query("SELECT * FROM users");
while ($user = mysql_fetch_array($userQuery,MYSQL_ASSOC))
{

$item = mysql_fetch_array(mysql_query("select * from user_equipment where equipped='Yes' and owner='$user[id]' and slot='Weapon'"));
$item11 = mysql_fetch_array(mysql_query("select * from user_equipment where equipped='Yes' and owner='$user[id]' and slot='Body'"));
$item22 = mysql_fetch_array(mysql_query("select * from user_equipment where equipped='Yes' and owner='$user[id]' and slot='Shield'"));
$item33 = mysql_fetch_array(mysql_query("select * from user_equipment where equipped='Yes' and owner='$user[id]' and slot='Head'"));
$item4 = mysql_fetch_array(mysql_query("select * from user_equipment where equipped='Yes' and owner='$user[id]' and slot='Belt'"));
$item5 = mysql_fetch_array(mysql_query("select * from user_equipment where equipped='Yes' and owner='$user[id]' and slot='Foot'"));
$item6 = mysql_fetch_array(mysql_query("select * from user_equipment where equipped='Yes' and owner='$user[id]' and slot='Pants'"));
$item7 = mysql_fetch_array(mysql_query("select * from user_equipment where equipped='Yes' and owner='$user[id]' and slot='Ring'"));
$item8 = mysql_fetch_array(mysql_query("select * from user_equipment where equipped='Yes' and owner='$user[id]' and slot='Neck'"));

$itemorb1 = mysql_fetch_array(mysql_query("select * from user_equipment where equipped='Yes' and owner='$user[id]' and slot='Orb1'"));

$itemorb2 = mysql_fetch_array(mysql_query("select * from user_equipment where equipped='Yes' and owner='$user[id]' and slot='Orb2'"));

$itemorb3 = mysql_fetch_array(mysql_query("select * from user_equipment where equipped='Yes' and owner='$user[id]' and slot='Orb3'"));

//$jewel = mysql_real_array(mysql_query("select * from user_equipment where equipped='Yes' and owner='$user[id]' and slot='Jewel'"));

$level = mysql_query("select *  from level");
       while ($lvl = mysql_fetch_array($level)){

$fat = $item[attack]+$item11[attack]+$item22[attack]+$item33[attack]+$item4[attack]+$item5[attack]+$item6[attack]+$item7[attack]+$item8[attack]+$itemorb1[attack]+$itemorb2[attack]+$itemorb3[attack]+$lvl[attack]+$user[jewel_attack];

$fhp = $item[hp]+$item11[hp]+$item22[hp]+$item33[hp]+$item4[hp]+$item5[hp]+$item6[hp]+$item7[hp]+$item8[hp]+$itemorb1[hp]+$itemorb2[hp]+$itemorb3[hp]+$lvl[hp]+$jewel[hp];

$fexp = $item[exp_per_hour]+$item11[exp_per_hour]+$item22[exp_per_hour]+$item33[exp_per_hour]+$item4[exp_per_hour]+$item5[exp_per_hour]+$item5[exp_per_hour]+$item8[exp_per_hour]+$itemorb1[exp_per_hour]+$itemorb2[exp_per_hour]+$itemorb3[exp_per_hour]+$lvl[eph]+$user[jewel_eph];

$crit = $item[critical]+$item11[critical]+$item22[critical]+$item33[critical]+$item4[critical]+$item5[critical]+$item6[critical]+$item7[critical]+$item8[critical]+$itemorb1[critical]+$itemorb2[critical]+$itemorb3[critical]+$user[jewel_critical];

$frage = $item[attacks_per_hour]+$item11[attacks_per_hour]+$item22[attacks_per_hour]+$item33[attacks_per_hour]+$item4[attacks_per_hour]+$item5[attacks_per_hour]+$item6[attacks_per_hour]+$item7[attacks_per_hour]+$item8[attacks_per_hour]+$itemorb1[attacks_per_hour]+$itemorb2[attacks_per_hour]+$itemorb3[attacks_per_hour]+$lvl[aph]+$user[jewel_aph];

$fmr = $item[max_attacks]+$item11[max_attacks]+$item22[max_attacks]+$item33[max_attacks]+$item4[max_attacks]+$item5[max_attacks]+$item6[max_attacks]+$item7[max_attacks]+$item8[max_attacks]+$itemorb1[max_attacks]+$itemorb2[max_attacks]+$itemorb3[max_attacks]+$lvl[max_attacks]+$userl[jewel_max];

$cap = $item[attack_cap]+$item11[attack_cap]+$item22[attack_cap]+$item33[attack_cap]+$item4[attack_cap]+$item5[attack_cap]+$item6[attack_cap]+$item7[attack_cap]+$item8[attack_cap]+$itemorb1[attack_cap]+$itemorb2[attack_cap]+$itemorb3[attack_cap]+$lvl[attack_cap]+$user[jewel_cap];


if($user['brewofdesolance'] == 1)
{ 
$fat = $fat+50;
$fhp = $fhp+100;
}



if($user[blackmarketEXP] = 0){
$exp = $fexp;
}

if($user[blackmarketEXP] == 1){
$fexp = $fexp+50;
}

if($user[blackmarketEXP] == 2){
$fexp = $fexp+100;
}

if($user[blackmarketFURY] = 1){
$fmr+=250;
}

if($user[blackmarketFURY] = 2){
$fmr+=250;
}

if($user[blackmarketFURYCAP] = 1){
$cap+=1;
}

if($user[blackmarketFURYCAP] = 2){
$cap+=1;
}

if($user[blackmarketFURYCAP] = 3){
$cap+=1;
}
if($user[blackmarketFURYCAP] = 4){
$cap+=1;
}
if($user[blackmarketFURYCAP] = 5){
$cap+=1;
}
if($user[blackmarketFURYCAP] = 6){
$cap+=1;
}
if($user[blackmarketFURYCAP] = 7){
$cap+=1;
}
if($user[blackmarketFURYCAP] = {
$cap+=1;
}
if($user[blackmarketFURYCAP] = 9){
$cap+=1;
}
if($user[blackmarketFURYCAP] = 10){
$cap+=1;
}

mysql_query("update users set critical=$crit where id=$user[id]");

mysql_query("update users set exp_per_turn=$fexp where $user[level] = $lvl[id] and id=$user[id]");

mysql_query("update users set attacks_per_turn=$frage where $user[level] = $lvl[id] and id=$user[id]");

mysql_query("update users set attack=$fat where $user[level] = $lvl[id] and id=$user[id]");

mysql_query("update users set hp=$fhp where $user[level] = $lvl[id] and id=$user[id]");

mysql_query("update users set max_attacks=$fmr where $user[level] = $lvl[id] and id=$user[id]");

mysql_query("update users set attacks_per_day_on_a_person=$cap where $user[level] = $lvl[id] and id=$user[id]");
}
}
echo "updated everyones stats.";

 

 

^^^ is the worthless script i was trying to optimize

 

below is what i added when the updates were needed.

 

if($_GET['action'])
{
//begin unequip functions.
if ($_GET['action'] == 'unequip')
{
$id = (int) $_GET["id"];
$owner = (int) $_GET["owner"];
$userid = $stat["id"];

if ($userid != $owner)
{
echo "<script>alert(\"Error: You cannot be here!\");</script>";
die();
}
$item_query = mysql_query("SELECT name,slot,attack,max_attacks,attack_cap,hp,exp_per_hour,attacks_per_hour FROM user_equipment where id = '".$id."'");
$name = mysql_result($item_query,0,"name");
$slot = mysql_result($item_query,0,"slot");
$attack = mysql_result($item_query,0,"attack");
$max_attacks = mysql_result($item_query,0,"max_attacks");
$attack_cap = mysql_result($item_query,0,"attack_cap");
$hp = mysql_result($item_query,0,"hp");
$eph = mysql_result($item_query,0,"exp_per_hour");
$aph = mysql_result($item_query,0,"attacks_per_hour");
$username = $stat["name"];
echo "<center><br />".$username." you have un-equipped you're ".$slot." ".$name."</center>";
//run the updates (hopefully speed it up.)
mysql_query("update users set attack=attack-'$attack' where id = '$owner'");
mysql_query("update users set max_attacks=max_attacks-'$max_attacks' where id = '$owner'");
mysql_query("update users set attacks_per_day_on_a_person=attacks_per_day_on_a_person-'$attack_cap' where id = '$owner'");
mysql_query("update users set hp=hp-'$hp' where id = '$owner'");
mysql_query("update users set exp_per_turn=exp_per_turn-'$eph' where id = '$owner'");
mysql_query("update users set attacks_per_turn=attacks_per_turn-'$aph' where id = '$owner'");
mysql_query("UPDATE user_equipment SET equipped='N' WHERE id = '".$id."' AND owner = '$owner'");
}

 

basically the same as action=equip for action=dequip..

 

 

sorry for rambling. ty

[kicken]

$item_query = mysql_query("SELECT name,slot,attack,max_attacks,attack_cap,hp,exp_per_hour,attacks_per_hour FROM user_equipment where id = '".$id."'");
$name = mysql_result($item_query,0,"name");
$slot = mysql_result($item_query,0,"slot");
$attack = mysql_result($item_query,0,"attack");
$max_attacks = mysql_result($item_query,0,"max_attacks");
$attack_cap = mysql_result($item_query,0,"attack_cap");
$hp = mysql_result($item_query,0,"hp");
$eph = mysql_result($item_query,0,"exp_per_hour");
$aph = mysql_result($item_query,0,"attacks_per_hour");
$username = $stat["name"];
echo "<center><br />".$username." you have un-equipped you're ".$slot." ".$name."</center>";
//run the updates (hopefully speed it up.)
mysql_query("update users set attack=attack-'$attack' where id = '$owner'");
mysql_query("update users set max_attacks=max_attacks-'$max_attacks' where id = '$owner'");
mysql_query("update users set attacks_per_day_on_a_person=attacks_per_day_on_a_person-'$attack_cap' where id = '$owner'");
mysql_query("update users set hp=hp-'$hp' where id = '$owner'");
mysql_query("update users set exp_per_turn=exp_per_turn-'$eph' where id = '$owner'");
mysql_query("update users set attacks_per_turn=attacks_per_turn-'$aph' where id = '$owner'");
mysql_query("UPDATE user_equipment SET equipped='N' WHERE id = '".$id."' AND owner = '$owner'");
}

 

Those queries could all be replaced by the following two queries:

mysql_query("
UPDATE users u
INNER JOIN user_equipment e ON e.id=$id
SET
   attack=u.attack-e.attack
   , max_attacks=u.max_attacks-e.max_attacks
   , attacks_per_day_on_a_person=u.attacks_per_day_on_a_person-e.attack_cap
   , hp=u.hp-e.hp
   , exp_per_turn=u.exp_per_turn-e.exp_per_hour
   , attacks_per_turn=u.attacks_per_turn-e.attacks_per_hour
WHERE
   u.id=$owner
");

mysql_query("
UPDATE user_equipment
SET
   equipped='N'
WHERE
   id=$id
   AND owner=$owner
");

 

The first takes care of updating all the stats fields on the users table by removing the values based on the user_equipment table. There is no need to select them first, you can just JOIN with the appropriate row and do the math in the query.

 

The second then sets the equipped flag to N for the specified item.

 

For equipping an item you'd just reverse the process.

[Psycho]

Personally, based on my understanding, I think all these update queries are completely unnecessary! As I said in my last post:

Depending on what you are really doing, this process may not even be needed. At least, it's usually not necessary to calculate sums within a cron job since that can be done in real time.

 

And, it seems that is exactly what you are trying to do. You are populating total values for hp, attack, experience, etc. based upon all the equipped items of the users. You should avoid trying to store "summation" data based upon other data unless there is a clear reason to do so. You can run into all sorts of problems due to race conditions and data just getting out of sync.

 

Using the query I provided previously you can dynamically determine the totals for hp, attack, experience, etc. with a single query. But, if you find that calculating those values dynamically (the best option, IMO) is too system intensive and you need to actually store those summed values, you don't even need two queries - just one will work no matter if the user added or removed a single item or multiple items. Just use the query I provided previously for the purposes of the update.

 

This is somewhat complicated, so I can't guarantee there are no errors since I don't have your database. But, the following query would update the totals based upon all the currently equipped items. No need to do a "set max_attacks=max_attacks-'$max_attacks'"

 

$query = "UPDATE users AS u

 JOIN (SELECT SUM(attack) AS attack, SUM(hp) AS hp, SUM(critical) as critical,
 SUM(attacks_per_hour) as attack_per_hour, SUM(max_attacks) AS max_attacks,
 SUM(attack_cap) as attack_cap
	 FROM user_equipment
	 WHERE equipped = 'Yes'
		 AND owner = '$userID'
	 ) as totals ON u.id = totals.owner

 SET u.attack = sums.attack, u.hp = sums.hp, u.critical = sums.critical,
	 u.attacks_per_hour = sums.attack_per_hour, u.max_attacks = sums.max_attacks
	 u.attack_cap = sums.attack_cap

 WHERE u.id = '$userID'";

 

With the above you can process multiple add/removes of equipment and then run a single query to update the totals for a user.