Assistance with speeding up a Query/Process

[ochaos]

Hi There,

I have a website that has about 11,000 fantasy football teams in it and I need to hit each one, find out their highest scoring players at each position and save that in a field in the DB.

Currently I have a query that grabs sorts all the players on the team by score DESC, then I read that into a page with PHP and check which scores are the highest. I'll them save the total score and put those players in a comma separated field in the DB.

This is no problem with one team, but when I run this against all 11K teams in the DB, it takes a long time. I'm on 9 minutes now and it's through about 3500 teams.

I need thoughts on how to perform this function faster.

I was wondering if I could break it up into 4 or 5 scripts and run them simultaneously or if there would be a better way to do it?

 

[r937]

since you have to go through all teams, indexing isn't going to help (indexing lets you retrieve a few rows, but if you need them all, indexing is not used, a table scan is the answer)

you're not reading the teams one at a time in a loop, are you?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ochaos]

Yes I am :bagoverhead:.

It looks like this.

for($i=10001;$i<11000;$i++){
    $winner_info = orderWinners($curr_week,$i);
    $winners = implode(',',$winner_info[0]);
    $week_score= $winner_info[1];
    $sql="UPDATE teams SET week_score= '$week_score', week_winners ='$winners' WHERE team_id='$i' AND week='$curr_week'";
    mysql_query($sql);
    print $sql.'<br />';
}

The orderWinners() function is a function I wrote that gets the player scores from the database sorted highest to lowest, it loops through the results and gives me the top QB, 2top RB's, 3 top WR's 2 top TE's and top flex (1 RB, WR or TE) then outputs it to be saved.

It looks like this

function orderWinners($curr_week,$teamid){
    include 'include/connect.php';
    $sql="SELECT * FROM rosters,players  WHERE tid='$teamid' AND players.pid=rosters.pid ORDER BY week_score DESC";
    $result = mysql_query($sql);
    $qb=0;$rb=0;$wr=0;$te=0;$pk=0;$def=0;$flex=0;
    $winners=array();
    $total_score=0;
    while ($row = mysql_fetch_assoc($result)) {
        if($flex==0){
            if((($wr==3)&&($row['pos']=="WR"))||(($rb==2)&&($row['pos']=="RB"))||($te==1)&&($row['pos']=="TE")){
                $winners[]=$row['pid'];
                $total_score = $total_score + $row['week_score'];
            $flex++;
            }
        }
        if(($qb==0)&&($row['pos']=="QB")){
            $winners[]=$row['pid'];
            $total_score = $total_score + $row['week_score'];
            $qb++;
        }
        if(($rb<2)&&($row['pos']=="RB")){
                $winners[]=$row['pid'];
                $total_score = $total_score + $row['week_score'];
                $rb++;
        }
        //print $wr.'<br />';
        if(($wr<3)&&($row['pos']=="WR")){
                $winners[]=$row['pid'];
                $total_score = $total_score + $row['week_score'];
                $wr++;
        }
        if(($te<1)&&($row['pos']=="TE")){
                $winners[]=$row['pid'];
                $total_score = $total_score + $row['week_score'];
                $te++;
        }
        if(($pk==0)&&($row['pos']=="PK")){
            $winners[]=$row['pid'];
            $total_score = $total_score + $row['week_score'];
            $pk++;
        }
        if(($def==0)&&($row['pos']=="Def")){
            $winners[]=$row['pid'];
            $total_score = $total_score + $row['week_score'];
            $def++;
        }
    }
    return array($winners,$total_score);
}

I know it's ugly and extremely inefficient, but I am looking at ways to make it better. So, if I could be pointed in the right direction, that would be awesome.

My DB tables look like this.

I have one table that has all the players info with their scores for the week.
Table: Players

player_id, week_score (other fields not important)

Then, I have the rosters table, with nearly 11 thousand teams ranging from 20 -30 players each, this table has about 225 thousand rows.
It looks like this
Table: Rosters

team_id, player_id (other fields not important)

And finally, I want to store the "winning" players in a third table.
Table: Teams

team_id, week_score, week_winners (other fields not important)

I think that my problem is that I can't think of a way to construct a query that would be able to handle the flex player mentioned above , if I could do that, I'd be able to eliminate this PHP looping stuff.

 

[r937]

i'm not sure i understand all your calculations, but you should be able to keep them in php

have you thought about eliminating the main loop?

SELECT *
FROM rosters
INNER
JOIN players
ON players.pid = rosters.pid
ORDER
BY rosters.team_id
, players.week_score DESC

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ochaos]

Good suggestion. Just running a couple of queries and doing the math here, this looks awesome.

Running the query to select 1 team and sort it DESC by score takes about .12 seconds, do that 11K times and that's around 20-25 minutes.

Running the query you suggested dropped this down to a ~40 second query that grabs all the data to be processed by PHP... I'm going through the same amount of records either way, so this should significantly cut down the time it takes to process this.

I'll give it a try and report back, thank you!

 

[r937]

even with a single query to retrieve everything, you'll still be doing 11k update statements

if you can do your calculations in sql, you can change the SELECT for the entire table to UPDATE



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ochaos]

Yeah, I'm going to have to play around and see if I can do the calculations in SQL.

Thanks for your help.

Just to try and simplify the PHP calculations above, here's what I need for the final results.

The most points for each position.
(1) QB
(2) RB's
(3) WR's
(1) TE
(1) PK
(1) Def
(1) Flex (Highest remaining of RB,WR or TE)

these positions are defined in the table
Players

player_id, week_score, pos

If I just needed to grab everything except the Flex, I could work my way through that, but I can't wrap my head around where to start with that part.