SQL query to find player with maximum runs

I have the following tables with me :

Table 1 Title : Runs

+------------+---------+
                    |    P_ID    |  Runs   |
                    +------------+---------+
                    |      1     | 100     |
                    |      2     | 45      |
                    |      3     | 35      |
                    |      1     | 90      |
                    |      2     | 85      |
                    +------------+---------+

Table 2 Title : Player

+------------+---------+
                    |    P_ID    |  Name   |
                    +------------+---------+
                    |    1       | Dhoni   |
                    |    2       | Sehawag |
                    |    3       | Sachin  |
                    |    4       | Dravid  |
                    |    5       | Kohli   |
                    +------------+---------+

I have to find the player name whose sum of runs is maximum.

I am new to MySQL and not able to figure out the correct/most efficient query for that, please help.

With the table of separate runs, making the sums will mean reading entire table each time so for performance it would be good to have the sums precomputed somewhere, maybe even in Player table, with updates either periodically (daily, each hour) or on insert/update in Runs table (trigger, ORM event or other ways).

But that may be not possible for your case, so query to compute them and find the max:

select
  sum(r.runs) total,
  r.p_id,
  p.name
from Runs r
join Player p using(p_id)
group by r.p_id
order by sum(r.runs) desc
limit 1;

Can be seen in action at http://sqlfiddle.com/#!9/27820/2

When two players have the same max, it will pick one of them (probably the one with lower p_id, but that does not have to be true). If it is not OK, define further requirements for this.

It would be good to have index on (p_id, runs) in Runs table so summing per player can be done fast, but then to find the max a filesort is still needed, so if you plan to have hundreds of thousands or millions of rows in Runs table, then you will probably really want to precompute it.

Players table only needs index in p_id, which is easily satisfied by it being primary key which seems quite natural.

StackExchange Database Administrators Q#105630, answer score: 7