I need to optimize a query for a ranking that is taking forever (the query itself works, but I know it's awful and I've just tried it with a good number of records and it gives a timeout).

I'll briefly explain the model. I have 3 tables: player, team and player_team. I have players, that can belong to a team. Obvious as it sounds, players are stored in the player table and teams in team. In my app, each player can switch teams at any time, and a log has to be mantained. However, a player is considered to belong to only one team at a given time. The current team of a player is the last one he's joined.

The structure of player and team is not relevant, I think. I have an id column PK in each. In player_team I have:

id          (PK)
player_id   (FK -> player.id)
team_id     (FK -> team.id)

Now, each team is assigned a point for each player that has joined. So, now, I want to get a ranking of the first N teams with the biggest number of players.

My first idea was to get first the current players from player_team (that is one record top for each player; this record must be the player's current team). I failed to find a simple way to do it (tried GROUP BY player_team.player_id HAVING player_team.id = MAX(player_team.id), but that didn't cut it.

I tried a number of querys that didn't work, but managed to get this working.

SELECT 
    COUNT(*) AS total,
    pt.team_id,
    p.facebook_uid AS owner_uid, 
    t.color 
FROM 
    player_team pt 
JOIN player p ON (p.id = pt.player_id)  
JOIN team t ON (t.id = pt.team_id) 
WHERE 
    pt.id IN (
        SELECT max(J.id) 
        FROM player_team J 
        GROUP BY J.player_id
    )  

GROUP BY 
    pt.team_id 
ORDER BY 
    total DESC 
LIMIT 50            

As I said, it works but looks very bad and performs worse, so I'm sure there must be a better way to go. Anyone has any ideas for optimizing this?

I'm using mysql, by the way.

Thanks in advance

Adding the explain. (Sorry, not sure how to format it properly)

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     t   ALL     PRIMARY     NULL    NULL    NULL    5000    Using temporary; Using filesort
1   PRIMARY     pt  ref     FKplayer_pt77082,FKplayer_pt265938,new_index    FKplayer_pt77082    4   t.id    30  Using where
1   PRIMARY     p   eq_ref  PRIMARY     PRIMARY     4   pt.player_id    1
2   DEPENDENT SUBQUERY  J   index   NULL    new_index   8   NULL    150000  Using index