Dead6re Posted February 25, 2008 Share Posted February 25, 2008 For some reason, my code is running really slowly whilst using the SELECT COUNT statements. It takes 10 seconds to run and return just 30 results. I tried using SELECT *something* then SELECT FOUND_ROWS() but that also takes a long time. I know I'm performing 60 odd queries, but surely it should be quick or can be optimized. I'm counting rows, not parsing data! <?php //error_reporting(E_ALL); set_time_limit(0); // Processing time $mtime = microtime(); $mtime = explode(" ",$mtime); $mtime = $mtime[1] + $mtime[0]; $starttime = $mtime; $MySQL = mysql_connect('localhost', 'root', '') or die('Could not connect to MySQL Database'); // Connect or die. // RegionID - Catch = 10000014 $qCatch = mysql_db_query('eve', "SELECT solarSystemID, solarSystemName FROM mapsolarsystems WHERE regionID = '10000014' ORDER BY solarSystemName ASC LIMIT 0, 30"); $Catch = array(); while ($row = mysql_fetch_assoc($qCatch)) { $ID = $row['solarSystemID']; $Catch[$ID]['name'] = $row['solarSystemName']; $qPlanet = mysql_db_query('eve', "SELECT COUNT(solarSystemID) FROM mapdenormalize WHERE solarSystemID = $ID AND groupID = 7"); $Planet = mysql_fetch_array($qPlanet); $Planet = $Planet[0]; mysql_free_result($qPlanet); $qMoon = mysql_db_query('eve', "SELECT COUNT(itemID) FROM mapdenormalize WHERE solarSystemID = $ID AND groupID = 8 LIMIT 1"); $Moon = mysql_fetch_array($qMoon); $Moon = $Moon[0]; mysql_free_result($qMoon); } foreach ($Catch as $ID => $Name) { echo "$ID - $Name ($Planet - $Moon)<br>"; } // End MySQL connection. mysql_close($MySQL); // Processing Time $mtime = microtime(); $mtime = explode(" ",$mtime); $mtime = $mtime[1] + $mtime[0]; $endtime = $mtime; $totaltime = ($endtime - $starttime); echo "<br>This page was created in ".$totaltime." seconds"; Quote Link to comment Share on other sites More sharing options...
Dead6re Posted February 25, 2008 Author Share Posted February 25, 2008 My orignal code = 5-10 seconds. My new code: This page was created in 0.311986923218 seconds YES!!! How I did it? Performing the 60 odd queries I was doing was slow. It was basically, lets loop through every system id until we have finished. Now, since we know I only need a list of systems and a count of the planets and moons, couldn't I just group the results and count those? From 60 queries down to 3, lets cheer! Yes. <?php //error_reporting(E_ALL); set_time_limit(0); // Processing time $mtime = microtime(); $mtime = explode(" ",$mtime); $mtime = $mtime[1] + $mtime[0]; $starttime = $mtime; $MySQL = mysql_connect('localhost', 'root', '') or die('Could not connect to MySQL Database'); // Connect or die. // RegionID - Catch = 10000014 $qCatch = mysql_db_query('eve', "SELECT solarSystemID, solarSystemName FROM mapsolarsystems WHERE regionID = '10000014' ORDER BY solarSystemName ASC LIMIT 0, 50"); $Catch = array(); $qPlanets = mysql_db_query('eve', "SELECT solarSystemID, COUNT(groupID) FROM mapdenormalize WHERE regionID = '10000014' AND groupID = 7 GROUP BY solarSystemID"); while ($row = mysql_fetch_array($qPlanets)) { $Catch[$row['solarSystemID']]['planets'] = $row['COUNT(groupID)']; } mysql_free_result($qPlanets); $qMoons = mysql_db_query('eve', "SELECT solarSystemID, COUNT(groupID) FROM mapdenormalize WHERE regionID = '10000014' AND groupID = 8 GROUP BY solarSystemID"); while ($row = mysql_fetch_array($qMoons)) { $Catch[$row['solarSystemID']]['moons'] = $row['COUNT(groupID)']; } mysql_free_result($qMoons); while ($row = mysql_fetch_assoc($qCatch)) { $ID = $row['solarSystemID']; $Planets = $Catch[$ID]['planets']; $Moons = $Catch[$ID]['moons']; $Catch[$ID]['name'] = $row['solarSystemName']; echo "$ID - $Name ($Planets - $Moons)<br>"; } /* foreach ($Catch as $ID => $Name) { echo "$ID - $Name<br>"; } */ // End MySQL connection. mysql_close($MySQL); // Processing Time $mtime = microtime(); $mtime = explode(" ",$mtime); $mtime = $mtime[1] + $mtime[0]; $endtime = $mtime; $totaltime = ($endtime - $starttime); echo "<br>This page was created in ".$totaltime." seconds"; Quote Link to comment Share on other sites More sharing options...
ucffool Posted February 25, 2008 Share Posted February 25, 2008 I fear I don't know enough about the database to say if there is a better way to pull the data you need (which is probably what you need). If it does take a while, how often does the output of this script CHANGE? If not often, could you put the output into a flat file and just read that regularly, and run this script daily? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.