1

I have an online rugby manager game. Every registered user has one team, and each team has 25 players at the beginning. There are friendly, league, cup matches.

I want to show for each player page the number of:

  • official games played,
  • tries,
  • conversions,
  • penalities,
  • dropgoals

and for each of the categories:

  • in this season;
  • in his career;
  • for national team;
  • for U-20 national team

I have two options:

    $query = "SELECT id FROM tbl_matches WHERE type='0' AND standing='1'";
    $query = mysql_query($query, $is_connected) or die('Can\'t connect to database.');
    while ($match = mysql_fetch_array($query)) {
        $query2 = "SELECT COUNT(*) as 'number' FROM tbl_comm WHERE matchid='$match[id]' AND player='$player' and result='5'";
         $query2 = mysql_query($query2, $is_connected) or die('Can\'t connect to database.');
         $try = mysql_fetch_array($query2);         
     }

This script searches every official match played by the selected player. Then gets the report for that match (about 20 commentary lines for every match) and check every line if the searched player had scored a try.

The problem is that in a few seasons there could be about 20 000 000 row in commentary page. Will my script load slowly (notable by users)?

The second option is to create a table with player stats, who will have about 21 cols.

What do you recommend that I do?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Florin Frătică
  • 587
  • 4
  • 7
  • 13
  • Player stats is my immediate thought. – Radu Jul 02 '11 at 21:30
  • Instead a `count(*)` try doing `count(colname)`; that will be faster – Rahul Jul 02 '11 at 21:32
  • 8
    @Rahul Not according to [this](http://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better/2710703#2710703). – trutheality Jul 02 '11 at 21:37
  • 1
    It would be helpful to see the schema of the matches and comments tables. There are sometimes good reasons for precomputing aggregate values; this could well be one of the times when it is better to do so. You could have the statistics for each player for each season, and aggregate across seasons for career statistics. – Jonathan Leffler Jul 03 '11 at 01:21
  • Pre-computing is the way to go. Too bad only Sql Server and Oracle has an out-of-the-box materialized view functionality http://www.ienablemuch.com/2011/06/materializing-views-in-sql-server.html For MySQL, tackle it with triggers – Michael Buen Jul 03 '11 at 01:43
  • Hmm.. it's only MySQL and Postgresql that is sorely missing that functionality http://en.wikipedia.org/wiki/Materialized_view – Michael Buen Jul 03 '11 at 01:45
  • 2
    I wonder if it'll be better to make statistic tables that are updated when the main table is modified. I wouldn't recommend using triggers though if you can help it but rather place all the logic in the model class for the table and wrap the changes in a transaction (e.g. UPDATE typeSomething + 1). If you have some kind of non-volatile caching scheme you don't need to place it in a table. Or if it is volatile then you would need a scheme to rebuild the statistics. – Yzmir Ramirez Jul 03 '11 at 08:11

1 Answers1

1

Why do all of those separate queries when you can just group them all together and get your count by id:

select tbl_matches.id,count(*)
  from tbl_matches join tbl_comm on tbl_matches.id = tbl_comm.matchid
 where tbl_comm.player = '$player'
   and tbl_comm.result = '5'
   and tbl_matches.type='0'
   and tbl_matches.standing='1'
 group by tbl_matches.id;

If you need additional columns, just add them to both the select columns and the group by column list.

Also: you should be extremely wary about substituting $player directly into your query. If that string isn't properly escaped, that could be the source of a SQL-injection attack.

EDIT: fixed query per Jonathan's comment

unpythonic
  • 4,020
  • 19
  • 20
  • It would be better to use the explicit JOIN notation. You've also mislaid the criteria on `m.type` and `m.standing` (and I'd strongly recommend prefixing every column with the appropriate table alias, so reference `c.player` and `c.result`). – Jonathan Leffler Jul 03 '11 at 01:35
  • @Florin: Check how fast this query runs, optimize it (if you haven't any indexes, it will be real slow with 20M rows) and only if it still slower than your needs, then consider materialized view. – ypercubeᵀᴹ Jul 03 '11 at 08:10
  • I'll make a new table for players stats. – Florin Frătică Jul 03 '11 at 09:07