MySQL performance issue

Question

I want to build a visual table where I can see the following things:
Team - Basketball stats about the team.
Number In League - The position that team is on each stat in the league.
League Average - The league average on each stat.
I have the following tables in my DB:

gamesstats: Have the stats on all games(what teams played and more) + link to stats table which holds all of the stat fields I want(2 links, one for the home team stat and one for the visitor team stat).

stats: Each row represents a game for one team(the stats such as 3 points attempts).

The situation is:
The "Team" row is easy to get and is fast(takes miliseconds).
The "League Average" row is easy and fast(same here).
The "Number In League" is however much slower(takes several seconds).

It is important for me that the creation won't take more than 5 seconds tops, but because I ask for the "Number In League" several times(3) in one page, it takes around 10-20 seconds.
The tables have around 100k entries.

Here is my implementation:

$p2pQuery = '100*avg(s.P2M)/avg(s.P2A)';
$p3pQuery = '100*avg(s.P3M)/avg(s.P3A)';
$ftpQuery = '100*avg(s.FTM)/avg(s.FTA)';
$effQuery = '(avg(s.TOT) + avg(s.AST) + avg(s.ST) + avg(s.PTS) + avg(s.P2M) +
              avg(s.P3M) + avg(s.FTM) + avg(s.RV) + avg(s.FV)) -
              (avg(s.TO) + avg(s.P3A) + avg(s.FTA) + avg(s.AG) + avg(s.CM))';

$whatWeWant = " avg(s.P2M) as P2M, avg(s.P2A) as P2A,  $p2pQuery as P2P,
                avg(s.P3M) as P3M, avg(s.P3A) as P3A,  $p3pQuery as P3P,
                avg(s.FTM) as FTM, avg(s.FTA) as FTA,  $ftpQuery as FTP,
                avg(s.OFFENSE) as OFFENSE, avg(s.DEF) as DEF, avg(s.TOT) as TOT, 
                avg(s.AST) as AST, avg(s.TO) as 'TO', avg(s.ST) as ST,
                avg(s.FV) as FV, avg(s.CM) as CM, avg(s.PTS) as PTS,
                $effQuery as EFF";

$allTeamsString = "SELECT t.ID as ID, $whatWeWant
                   FROM teams as t, gamesstats as gs, stats as s
                   WHERE gs.LeagueId = $ESCleague 
                   AND gs.SeasonStart = $ESCseason
                   AND ((gs.HomeTeamID = t.ID AND gs.VisitorStat = s.ID) 
                   OR (gs.VisitorTeamID = t.ID AND gs.HomeStat = s.ID))
                   GROUP BY t.ID";

$allTeamsQuery = MyQuery($allTeamsString);
$allTeams = resultSet2RowArray($allTeamsQuery, true);

$stat2 = array();
$stat2['Flag'] = 'In League';
//$fields holds stuff like P2M AST EFF and such as you can see in $whatWeWant
foreach($fields as $field):
    $col = $field['colName'];
    $counter = 1;
    foreach($allTeams as $team)
        if($stat1[$col] < $team[$col])
            $counter++;
    $stat2[$col] = $counter;
endforeach;

Yes, I would think so too, but getting this sql for one team, by adding that the home or visitor team id are restricted, is fast. — Vadiklk
– Vadiklk, Commented Jun 4, 2011 at 20:08

Johan · Accepted Answer · 2011-06-04 20:33:27Z

2

If we keep in mind that AVG(x) = SUM(x) / count(x)
and that AVG(x) + AVG(y) = SUM(x+y) / count(x)

Then we can speed up this query using SUM() and COUNT().
However this will only work if there are no null rows!

$p2pQuery = '100*SUM(s.P2M)/SUM(s.P2A)'; 
$p3pQuery = '100*SUM(s.P3M)/SUM(s.P3A)';
$ftpQuery = '100*SUM(s.FTM)/SUM(s.FTA)';
$effQuery = '(SUM(s.TOT + s.AST +s.ST + s.PTS + s.P2M +
              s.P3M + s.FTM + s.RV + s.FV)) -
              (SUM(s.TO + s.P3A + s.FTA + s.AG + s.CM))';

$whatWeWant = " SUM(s.P2M) as P2M, SUM(s.P2A) as P2A,  $p2pQuery as P2P,
                SUM(s.P3M) as P3M, SUM(s.P3A) as P3A,  $p3pQuery as P3P,
                SUM(s.FTM) as FTM, SUM(s.FTA) as FTA,  $ftpQuery as FTP,
                SUM(s.OFFENSE) as OFFENSE, SUM(s.DEF) as DEF, SUM(s.TOT) as TOT, 
                SUM(s.AST) as AST, SUM(s.TO) as 'TO', SUM(s.ST) as ST,
                SUM(s.FV) as FV,  SUM(s.CM) as CM, SUM(s.PTS) as PTS,
                $effQuery as EFF";

$allTeamsString = "SELECT t.ID as ID, count(*) as divider, $whatWeWant
                   FROM teams as t
                   INNER JOIN gamesstats as gs 
                     ON (gs.HomeTeamID = t.ID) OR (gs.VisitorTeamID = t.ID)
                   INNER JOIN stats as s 
                     ON (gs.VisitorStat = s.ID) OR (gs.HomeStat = s.ID)
                   WHERE gs.LeagueId = '$ESCleague' 
                   AND gs.SeasonStart = '$ESCseason'
                   GROUP BY t.ID";

You can divide the result by field 'divider' if you need to in the php-code.
First see if the SQL-query runs any quicker.

Remarks

Don't use SQL '89 syntax, use SQL'92 instead
Please don't use an implicit where join. It's confusing and error prone. Use explicit SQL'92 join syntax instead. Then you can separate the join criteria from the filter criteria.
See the rewritten syntax in the answer.

Close that SQL-injection hole
And don't forget to enclose injected $vars in a query in ' single quotes if you don't want a SQL-injection attack to happen to you.

answered Jun 4, 2011 at 20:33

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ypercubeᵀᴹ Over a year ago

The INNER JOINs with ON x OR y is playing a role in the slowness, too.

Johan Over a year ago

@ypercube, yes I noticed that after I rewrite the query in SQL'92 but how to fix that slowness?

ypercubeᵀᴹ Over a year ago

Divide the query into two (with only simple joins), separate for home and visitor, do the group by and calculate SUMs and COUNT in both. Then join the two results - and use AVG = (SUM(x)+SUM(y))/(COUNT(x)+COUNT(y)) . It may be faster, it may be slower, only testing will show.

ypercubeᵀᴹ Over a year ago

@Vadiklk: The OR complexity in the queries comes from having the Home and Visitor ids in separate columns in your gamesstats table. You can consider normalizing gamesstats by putting 1 row into 2 rows, one for home and one for visitor, and adding an extra column (bit field, Home/Visitor).

Vadiklk Over a year ago

As I understand, I can make it faster by doing little tweaks here and there. I will try them later today, but the question is whether there is a faster solution to what I want to achieve than what I wrote with modifications?

Collectives™ on Stack Overflow

MySQL performance issue

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related