CODEWITHSUNDEEP
mysqldatabase-design
Florin Frătică
Florin Frătică

Reputation: 587

Select Count from a table with 20 000 000 rows

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:

and for each of the categories:

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?

Upvotes: 1

Views: 421

Answers (1)

unpythonic
unpythonic

Reputation: 4070

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

Upvotes: 1

Related Questions