[RESOLVED] correct query and speed

[jonzy]

I have a query running on my database that seems to be really slow, so I'm sure I am taking the long way around on it. Hopefully someone can shed some light as to what I am doing wrong.

Tables are:
player_stats -> id,playerid,goals,assists,minutes,game_id,team_id,div_id,seasonid
players -> id,lname,fname,teamid,number,position
teams -> id,name,division
divisions -> id,name,seasonid

My query is this,

$stats_sql = "SELECT * FROM player_stats,teams,divisions,players WHERE player_stats.seasonid = '".$sid."' AND "
."player_stats.div_id = teams.division AND player_stats.div_id = divisions.id AND "
."teams.id = player_stats.team_id AND "
."players.id = player_stats.playerid AND players.position != 'G'";

Which is selecting about 8,000 entries and then I add up the goals and assists, and then run a function to sort the entries so I can get the top 25 point scorers.
Obviously I see that I shouldn't be selecting everything in all tables, but I'm sure it goes much deeper than that. I also am not sure if using joins will work better on what I am trying to do. ->NOOB<-

The final display is the players first and last name, division name, team name and the added goals and assists to equal points.

Thanks for your help...

[NogDog]

One thing to do is only select the columns you actually want to do anything with. Otherwise you'll be returning all 21 columns with each result row:

SELECT fname, lname, teams.name, goals + assists AS total_points FROM ...

Now you'll just be returning the four values you want to output: 'fname', 'lname', 'name' (of the team), and 'total_points'.

[jonzy]

Thanks! I did change that, and also did the query test in phpMyAdmin and it went really fast. I think something in my code with adding the results to an array or sorting the results may be taking lots of time.

First I add the results to an array so I have total goals, assists, and points for each player, and then I have a sort function so I can show the top 10 or whatever:

sc = 0;
$p_array = Array();
   	while($sc < $statcount) {
	$playerid = mysql_result($result_stats,$sc,"players.id");
	$p_array[$playerid][goals] = $p_array[$playerid][goals] + mysql_result($result_stats,$sc,"player_stats.goals");
	$p_array[$playerid][assists] = $p_array[$playerid][assists] + mysql_result($result_stats,$sc,"player_stats.assists");
	$p_array[$playerid][pts] = $p_array[$playerid][pts] + mysql_result($result_stats,$sc,"player_stats.goals") + mysql_result($result_stats,$sc,"player_stats.assists");
	$p_array[$playerid][fname] = mysql_result($result_stats,$sc,"players.fname");
	$p_array[$playerid][lname] = mysql_result($result_stats,$sc,"players.lname");
	$p_array[$playerid][team] = mysql_result($result_stats,$sc,"teams.name");
	$sc++;
	}

uasort($p_array, 'Compare_stats');

My sort function:

function Compare_stats($ar1, $ar2)
{
   if ($ar1['pts']<$ar2['pts'])
      return 1;
   else if ($ar1['pts']>$ar2['pts'])
      return -1;
   if ($ar1['lname']<$ar2['lname'])
      return -1;
   else if ($ar1['lname']>$ar2['lname'])
      return 1;
   if ($ar1['fname']>$ar2['fname'])
      return 1;
   else if ($ar1['fname']<$ar2['fname'])
      return -1;
   return 0;
}

Is there a better way to do that?

[NogDog]

If you want to list the top 10 scorers, for example, then probably the fastest thing would be to let the DB do it for you:

SELECT fname, lname, teams.name, goals + assists AS total_points FROM 
...tables and where clause, then...
ORDER BY total_points DESC, lname, fname LIMIT 10

This will only return the top 10 scorers, with identical scores sorted by lname then fname.

[jonzy]

Would that work where you have stats for each player in multiple games? Beacuse I have to query all the individual stats for each game played, so John Doe will have 10 game stat entries in the database where I have to add the goals, assists, and create points. And that goes for many players.

That looks like it would work good to show top scorers of one game only.

Thanks!

[NogDog]

Then you'd probably want to do a GROUP BY:

SELECT player_stats.id, fname, lname, teams.name, SUM(goals + assists) AS total_points
FROM ...<tables and where clause, then>...
GROUP BY id ORDER BY total_points DESC, lname, fname LIMIT 10

[jonzy]

Excellent... just did my query in phpMyAdmin and it came out perfect! Went from 30 seconds to 0.1353 seconds. Thanks for your help.

[NogDog]

Glad it worked.