Help speeding up queries

[ameriblog]

I have a query that is used to calculate W/L records for teams based on games. I have three tables:

 

TABLE wvhs_gm

gameID (pk)

game_date

game_year

game_fixed

 

TABLE wvhs_gm_info

infoID (pk)

gameID (foreign key)

teamID (foreign key)

team_score

 

TABLE wvhs_tm

teamID

team_name

team_won

team_lost

team_gp

etc

 

I have the following code that I use to calculate the record, but it takes forever, a good 5+ minutes. I have 11543 or so games in the database and that means I have over 23000 info entries in the database. The reason there is so many is all scores from 2004, 2005, 2006 are in there and I am starting to add 2007. This is so I can keep historical records and run retro-ratings from these games.

 

Here's the code:

 

// START CLEARING W, L, T, ETC

$reset_rs = $conn->Execute ( "SELECT * FROM ncaa_tm WHERE team_year = " . $_GET['year'] . " ORDER BY teamID DESC" ) or die ( $conn->ErrorMsg() );

echo "Starting clearing fields ...";

while ( ! $reset_rs->EOF ) {

$sql = "UPDATE ncaa_tm SET team_mpirankold = " . $reset_rs->Fields("team_mpirank") . ", team_won = 0, team_lost = 0, team_gp = 0, team_cwon = 0, team_clost = 0, team_cgp = 0, team_pf = 0, team_pa = 0, team_ww = 0, team_sostotal = 0, team_awp = 0.5 WHERE teamID = " . $reset_rs->Fields("teamID") . "";
$add_rating_rs = $conn->Execute($sql) or die ( $conn->ErrorMsg() );

$reset_rs->MoveNext(); }

echo "Complete!<br><hr><br>";

// END CLEARING W, L, T, ETC

//

// START CALCULATING W, L

$record_rs = $conn->Execute ( "SELECT * FROM ncaa_gm_info, ncaa_gm WHERE ncaa_gm_info.gameID = ncaa_gm.gameID AND game_year = " . $_GET['year'] . " AND game_fixed = 1 ORDER BY infoID ASC" ) or die ( $conn->ErrorMsg() );

echo "Start calculating record ... ";

while ( ! $record_rs->EOF ) {

$team_rs = $conn->Execute ( "SELECT * FROM ncaa_tm WHERE teamID = " . $record_rs->Fields("teamID") . "" ) or die ( $conn->ErrorMsg() );
$team = $team_rs->Fields("teamID");
$team_gp = $team_rs->Fields("team_gp");	
$team_won = $team_rs->Fields("team_won");
$team_lost = $team_rs->Fields("team_lost");	
$team_pf = $team_rs->Fields("team_pf");
$team_pa = $team_rs->Fields("team_pa");
$team_cwon = $team_rs->Fields("team_cwon");
$team_clost = $team_rs->Fields("team_clost");
$team_cgp = $team_rs->Fields("team_cgp");
$team_conf = $team_rs->Fields("team_conf");

$opp_rs = $conn->Execute ( "SELECT * FROM ncaa_gm_info, ncaa_tm WHERE gameID = " . $record_rs->Fields("gameID") . " AND ncaa_gm_info.teamID != " . $record_rs->Fields("teamID") . " AND ncaa_gm_info.teamID = ncaa_tm.teamID" ) or die ( $conn->ErrorMsg() );
$opp_conf = $team_rs->Fields("team_conf");

// IF TEAM WON
if ( $record_rs->Fields("team_score") > $opp_rs->Fields("team_score") )  {
	$team_gp = $team_gp + 1;
	$team_won = $team_won + 1;
	$team_pf = $team_pf + $record_rs->Fields("team_score");
	$team_pa = $team_pa + $opp_rs->Fields("team_score");

	if ( $team_rs->Fields("team_conf") == $opp_rs->Fields("team_conf") )  {
		$team_cgp = $team_cgp + 1;
		$team_cwon = $team_cwon + 1;		
	} else {
		$team_cgp = $team_cgp + 0;
		$team_cwon = $team_cwon + 0;
	}

	$sql = "UPDATE ncaa_tm SET team_cwon = $team_cwon, team_cgp = $team_cgp, team_pf = $team_pf, team_pa = $team_pa, team_gp = $team_gp, team_won = $team_won WHERE teamID = $team";
	$add_rating_rs = $conn->Execute($sql) or die ( $conn->ErrorMsg() );

}

// IF TEAM LOST
if ( $opp_rs->Fields("team_score") > $record_rs->Fields("team_score") )  {
	$team_gp = $team_gp + 1;
	$team_lost = $team_lost + 1;
	$team_pf = $team_pf + $record_rs->Fields("team_score");
	$team_pa = $team_pa + $opp_rs->Fields("team_score");

	if ( $team_rs->Fields("team_conf") == $opp_rs->Fields("team_conf") )  {
		$team_cgp = $team_cgp + 1;
		$team_clost = $team_clost + 1;		
	} else {
		$team_cgp = $team_cgp + 0;
		$team_clost = $team_clost + 0;
	}

	$sql = "UPDATE ncaa_tm SET team_clost = $team_clost, team_cgp = $team_cgp, team_pf = $team_pf, team_pa = $team_pa, team_gp = $team_gp, team_lost = $team_lost WHERE teamID = $team";
	$add_rating_rs = $conn->Execute($sql) or die ( $conn->ErrorMsg() );

}

$record_rs->MoveNext(); }

echo "Complete!<br><hr><br>";

// END CALCULATING W, L

 

What I would like to do is speed up the process, if possible. I have about 3800 games per year...

[teng84]

dont use * on your select

[ameriblog]

dont use * on your select

 

Tried id. Didn't really help.

 

I think I need to change my queries up a bit. I think the $record_rs query needs to get only the games, then within the loop the $team_rs query and $opp_rs query should get the info from wvhs_gm_info db where gameID is equal to the gameID from $record_rs and team.teamID = wvhs_gm_info.teamID.

 

I tried a variation of this and it didn't work. It wasn't computing the records correctly...

[ameriblog]

Actually, my server is doing odd stuff right now. I have stopped the program, so it isn't running/updating the WL, but if I go to the results page and just keep reloading it, the results change every time I reload it, like the updating file is running.

[jitesh]

Set indexind on search feilds.

 

example

 

TABLE wvhs_gm_info

infoID (pk)

gameID (foreign key)              // Set index

teamID (foreign key)              // Set index

team_score

 

in above table you may search records by gameId or teamID

 

[Yesideez]

If you use SELECT * in a query then every field is being pulled. If you're only dealing with a couple fields or even one then it's best to call only those you need.

 

Secondly, if you surround your PHP with {code} tags (replace braces for square brackets) then your code will be much easier to read.