MySQL Queries (Slow)

[Dead6re]

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"; 

[Dead6re]

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"; 

[ucffool]

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?