Query taking a long time to execute

Anon

This takes rather long....is there some way to optimize this?

for ($year=1975; $year < 2001; $year++)
{

$query = "SELECT sum(killed) AS sumKilled FROM myTable WHERE DisGroup LIKE 'Natural' AND year LIKE $year";
$result = mysql_query($query,$linkID);


$row = mysql_fetch_assoc($result);
$databarx[]=$year;
$databary[] = $row["sumKilled"];

}
i'm putting the results into an array to use in a bar chart..

Anon

maybe:

SELECT sum(killed) AS sumKilled FROM myTable WHERE DisGroup LIKE 'Natural'
group by year

vocoder wrote:

This takes rather long....is there some way to optimize this?

for ($year=1975; $year < 2001; $year++)
{

$query = "SELECT sum(killed) AS sumKilled FROM myTable WHERE DisGroup LIKE 'Natural' AND year LIKE $year";
$result = mysql_query($query,$linkID);


$row = mysql_fetch_assoc($result);
$databarx[]=$year;
$databary[] = $row["sumKilled"];

}
i'm putting the results into an array to use in a bar chart..

Anon

Yes,

Step 1:
Make sure that your year field is a date field.

Step 2:
Create an index on mytable.year

Step 3:
Try the query again as:

$Query = "select year,sum(killed) AS sumKilled FROM myTable WHERE DisGroup LIKE 'Natural' and year between '1971' and '2001' group by year order by year"

Should speed things up a bit.

Anon

Why not do it in one query?

SELECT sum(killed) AS sumKilled, year FROM myTable WHERE DisGroup LIKE 'Natural' AND year >= 1975 AND year < 2001 GROUP BY year;

Also check your indexing.

Anon

thanks, that did the trick...and i took the query out of the loop...works very fast now..thanks again