MYSQL Query Optimisation Help

[martmitch]

Apache/2.2.17 (Unix) mod_ssl/2.2.17

MYSQL: 5.0.92-community

 

I'm having issues with query optimization for the following snippet, the issue is my MYSQL knowledge is quite basic and the table this statement is calculating data from has over 1 million records

 

The code below selects two timestamps from the database of all tests taken by visitors of the specified country, adds them together and then calculates the average.

 

$sql_age_country_1="SELECT agestamp,deathstamp FROM stats where country='UK'";
$res_age_country_1 = mysql_query($sql_age_country_1);
$num_age_country_1=mysql_numrows($res_age_country_1);

$i_age_country_1=0;

while ($i_age_country_1 < $num_age_country_1) {

$agestamp_1=mysql_result($res_age_country_1,$i_age_country_1,"agestamp");
$deathstamp_1=mysql_result($res_age_country_1,$i_age_country_1,"deathstamp");

$stamp_total_1=$stamp_total_1+$deathstamp_1+$agestamp_1;

++$i_age_country_1;}

$AverageAge_1=$stamp_total_1/$num_age_country_1;

//Calculate average years from total
$Years_1=$AverageAge_1/31556926;
$YearsFormatted_1=number_format($Years_1, 1);

echo "Average for Canada is : $YearsFormatted_1";

 

The code does work as if I change the country to one with less records (Canada for example) I dont have the script timeout issue, however with any countries containing 150k+ records I am experiencing script execution timeout errors because it is hitting the 30 second limit on my server

 

Can anyone suggest optimizations to the above that could help me out?

 

[DavidAM]

Two things I would do here:

 

1) Make sure that country is indexed in the table. If it is not, then that query will have to do a table scan (it will read EVERY row in the table).

 

2) Let the database engine do all of the work:

 

SELECT COUNT(*) AS CountryCount, SUM(agestamp) AS AgeTotal, SUM(deathstamp) AS DeathTotal 
FROM stats 
WHERE country='UK'

 

Then PHP only has to process ONE row of data. This assumes that agestamp and deathstamp are defined as numeric fields (i.e. integer, decimal, etc).

[martmitch]

Wow thank you so much I cannot believe the difference, from timing out previously to calculating the stat in 1 second!

 

Thanks again, you've been a great help and have solved a long running problem!