mysql - Select Count from a table with 20 000 000 rows -

i have online rugby manager game. every registered user has 1 team, , each team has 25 players @ beginning. there friendly, league, cup matches.

i want show each player page number of:

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

and each of categories:

• in season;
• in career;
• for national team;
• for u-20 national team

i have 2 options:

    $query = "select id tbl_matches type='0' , standing='1'";     $query = mysql_query($query, $is_connected) or die('can\'t connect database.');     while ($match = mysql_fetch_array($query)) {         $query2 = "select count(*) 'number' tbl_comm matchid='$match[id]' , player='$player' , result='5'";          $query2 = mysql_query($query2, $is_connected) or die('can\'t connect database.');          $try = mysql_fetch_array($query2);               }  

this script searches every official match played selected player. gets report match (about 20 commentary lines every match) , check every line if searched player had scored try.

the problem in few seasons there 20 000 000 row in commentary page. script load (notable users)?

the second option create table player stats, have 21 cols.

what recommend do?

why of separate queries when can group them , count id:

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

if need additional columns, add them both select columns , group column list.

also: should extremely wary substituting $player directly query. if string isn't escaped, source of sql-injection attack.

edit: fixed query per jonathan's comment