How to find the top 10 most popular values in a comma separated list with PHP & MYSQL

Question

I am new to all of this and I have Googled and searched on here, but to no avail. Using google and some of the responses here, I've managed to solve a separate problem, but this is what I'm really interested in and am wondering if this is even possible/how to accomplish it.

I have mysql table that looks like this:

id       type of game       players               timestamp
1        poker              a,b,c,d,e,f,g,h       2011-10-08 08:00:00
2        fencing            i,j,k,l,m,n,o,p       2011-10-08 08:05:00
3        tennis             a,e,k,g,p,o,d,z       2011-10-08 08:10:00
4        football           x,y,f,b               2011-10-08 08:15:00

There are 7 types of games, and either 4 or 8 players separated by commas for each gametype. However, the players are IRC nicknames so potentially there could be new players with unique nicknames all the time.

What I am trying to do is look in the players column of the entire table and find the top 10 players in terms of games played, regardless of the gametype, and print it out to a website in this format, e.g.:

Top 10 Players:

1. a (50 games played)
2. f (39 games played)
3. o (20 games played) ......

10 g (2 games played)

Does anyone have any idea how to accomplish this? Any help is appreciated! Honestly, without this website I would not have even come this fair in my project!

Answer 1

My suggestion is that you don't keep a list of the players for each game in the same table, but rather implement a relationship between a games table and a players table.

The new model could look like:

TABLE Games:

id       type of game       timestamp
1        poker              2011-10-08 08:00:00
2        fencing            2011-10-08 08:05:00
3        tennis             2011-10-08 08:10:00
4        football           2011-10-08 08:15:00

TABLE Players:

id       name
1        a
2        b
3        c
..       ..

TABLE PlayersInGame:

id      idGame     idPlayer   current
1       1          1          true      //Player a is currently playing poker

When a player starts a game, add it to the PlayersInGame table.

When a player exits a game, set the current status to false.

To retrieve the number of games played by a player, query the PlayersInGame table.

SELECT COUNT FROM PlayersInGame WHERE idPlayer=1

For faster processing you need to de-normalize(not actually denormalization, but i don't know what else to call it) the table and keep track of the number of games for each player in the Players table. This would increase the table size but provide better speed.

So insert column games played in Players and query after that:

SELECT * FROM Players ORDER BY games_played DESC LIMIT 10

EDIT:

As Ilmari Karonen pointed out, to gain speed from this you must create an INDEX for the column games_played.

Answer 2

Unless you have a huge number of players, you probably don't need the denormalization step suggested at the end of Luchian Grigore's answer. Assuming tables structured as he initially suggests, and an index on PlayersInGame (idPlayer), the following query should be reasonably fast:

SELECT
  name,
  COUNT(*) AS games_played
FROM
  PlayersInGame AS g
  JOIN Players AS p ON p.id = g.idPlayer
GROUP BY g.idPlayer
ORDER BY games_played DESC
LIMIT 10

This does require a filesort, but only on the grouped data, so its performance will only depend on the number of players, not the number of games played.

Ps. If you do end up adding an explicit games_played column to the player table, do remember to create an index on it — otherwise the denormalization will gain you nothing.