This script while small there is more then 1000 entries in the table it checks to update on each update process. Can you help perfect this code for optimal performance. <? //include connection file here $getgames = mysql_query("SELECT round,attin,attout,attindown,attoutdown FROM $tab[game] WHERE starts<$time AND ends>$time ORDER BY round ASC;"); while ($game = mysql_fetch_array($getgames)) { if (!fetch("SELECT lastran FROM r$game[0]_$tab[cron] WHERE cronjob='cranks';")) { mysql_query("INSERT INTO r$game[0]_$tab[cron] VALUES ('cranks','$time');"); } else { mysql_query("UPDATE r$game[0]_$tab[cron] SET lastran='$time' WHERE cronjob='cranks'"); } $downin = $game[1]-2; $downon = $game[1]+2; mysql_query("UPDATE r$game[0]_$tab[pimp] SET attin=attin-$game[3] WHERE attin<='$game[1]';"); mysql_query("UPDATE r$game[0]_$tab[pimp] SET attin='$downin' WHERE attin>'$game[1]';"); mysql_query("UPDATE r$game[0]_$tab[pimp] SET attout=attout-$game[4] WHERE id>0;"); mysql_query("UPDATE r$game[0]_$tab[pimp] SET attin='0' WHERE attin>'4000';"); mysql_query("UPDATE r$game[0]_$tab[pimp] SET attout='0' WHERE attout>'4000';"); } ?> PHP: Any and all help is greatly appreciated. Thanks, Ted
Can you change your code to this? This will help benchmark the slow areas. Post the response back here. <? //include connection file here $time_start = microtime(true); $getgames = mysql_query("SELECT round,attin,attout,attindown,attoutdown FROM $tab[game] WHERE starts<$time AND ends>$time ORDER BY round ASC;"); $time1 = microtime(true); while ($game = mysql_fetch_array($getgames)) { if (!fetch("SELECT lastran FROM r$game[0]_$tab[cron] WHERE cronjob='cranks';")) { mysql_query("INSERT INTO r$game[0]_$tab[cron] VALUES ('cranks','$time');"); } else { mysql_query("UPDATE r$game[0]_$tab[cron] SET lastran='$time' WHERE cronjob='cranks'"); } $downin = $game[1]-2; $downon = $game[1]+2; $time2 = microtime(true); mysql_query("UPDATE r$game[0]_$tab[pimp] SET attin=attin-$game[3] WHERE attin<='$game[1]';"); $time3 = microtime(true); mysql_query("UPDATE r$game[0]_$tab[pimp] SET attin='$downin' WHERE attin>'$game[1]';"); $time4 = microtime(true); mysql_query("UPDATE r$game[0]_$tab[pimp] SET attout=attout-$game[4] WHERE id>0;"); $time5 = microtime(true); mysql_query("UPDATE r$game[0]_$tab[pimp] SET attin='0' WHERE attin>'4000';"); $time6 = microtime(true); mysql_query("UPDATE r$game[0]_$tab[pimp] SET attout='0' WHERE attout>'4000';"); $time7 = microtime(true); echo " Total time: ".($time7-$time_start)."<br /> Time 1: ".($time1 - $time_start)."<br /> Time 2: ".($time2 - $time1)."<br /> Time 3: ".($time3 - $time2)."<br /> Time 4: ".($time4 - $time3)."<br /> Time 5: ".($time5 - $time4)."<br /> Time 6: ".($time6 - $time5)."<br /> Time 7: ".($time7 - $time6)."<br /> "; } ?> PHP:
Total time: 0.122368097305<br /> Time 1: 0.000408887863159<br /> Time 2: 0.000288009643555<br /> Time 3: 0.0244660377502<br /> Time 4: 0.0175199508667<br /> Time 5: 0.042200088501<br /> Time 6: 0.01819896698<br /> Time 7: 0.0192861557007<br /> Total time: 0.180064916611<br /> Time 1: 0.000408887863159<br /> Time 2: 0.124646186829<br /> Time 3: 0.00830602645874<br /> Time 4: 0.00939297676086<br /> Time 5: 0.0100729465485<br /> Time 6: 0.0164670944214<br /> Time 7: 0.0107707977295<br />
Are you currently seeing any performance problems. Your database speed is a tiny bit slow on a few queries, but nothing that would cause a major problem unless you are running 100 of these per user-request.