Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Currently the query takes ~ 0.2 seconds. Is this the best it gets? Because it feels rather slow for the amount of data I have in my database. I've tried creating an inner join out of it but I can't get it to work.

SELECT player_id,
(
    SELECT sum(kills)
    FROM lcs_result
    where lcs_result.riot_match_id in (
                                        SELECT lcs_result.riot_match_id 
                                        FROM lcs_result 
                                        WHERE lcs_result.player_id = t.player_id
                                    ) and lcs_result.team_id=t.team_id
) as total_kills_in_team,
(
    SELECT sum(total_gold)
    FROM lcs_result
    where lcs_result.riot_match_id in (
                                        SELECT lcs_result.riot_match_id 
                                        FROM lcs_result 
                                        WHERE lcs_result.player_id = t.player_id
                                    ) and lcs_result.team_id=t.team_id
) as total_gold_in_team
FROM lcs_result as t
group by player_id;

Changing it to EXISTS doesn't have any impact on performance.

EXPLAIN:

+----+--------------------+------------+-------+-----------------------+-------------------+---------+------+------+-------------------------------------+
| id | select_type        | table      | type  | possible_keys         | key               | key_len | ref  | rows | Extra                               |
+----+--------------------+------------+-------+-----------------------+-------------------+---------+------+------+-------------------------------------+
|  1 | PRIMARY            | t          | index | player_id,player_id_2 | player_id         | 5       | NULL | 1340 | NULL                                |
|  4 | DEPENDENT SUBQUERY | lcs_result | ref   | lcs_result_ibfk_3     | lcs_result_ibfk_3 | 4       | func |   41 | NULL                                |
|  4 | DEPENDENT SUBQUERY | lcs_result | ref   | player_id,player_id_2 | player_id         | 5       | func |    7 | Using where; FirstMatch(lcs_result) |
|  2 | DEPENDENT SUBQUERY | lcs_result | ref   | lcs_result_ibfk_3     | lcs_result_ibfk_3 | 4       | func |   41 | NULL                                |
|  2 | DEPENDENT SUBQUERY | lcs_result | ref   | player_id,player_id_2 | player_id         | 5       | func |    7 | Using where; FirstMatch(lcs_result) |
+----+--------------------+------------+-------+-----------------------+-------------------+---------+------+------+-------------------------------------+

CREATE:

| lcs_result | CREATE TABLE `lcs_result` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `player_id` int(11) DEFAULT NULL,
  `kills` int(11) DEFAULT NULL,
  `deaths` int(11) DEFAULT NULL,
  `assists` int(11) DEFAULT NULL,
  `total_gold` int(11) DEFAULT NULL,
  `minions_killed` int(11) DEFAULT NULL,
  `match_result` int(11) DEFAULT NULL,
  `region` varchar(20) DEFAULT NULL,
  `date_played` datetime DEFAULT NULL,
  `date_entered` datetime DEFAULT NULL,
  `riot_match_id` int(11) DEFAULT NULL,
  `match_time_seconds` int(11) DEFAULT NULL,
  `role` varchar(120) DEFAULT NULL,
  `team_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `player_id` (`player_id`),
  KEY `lcs_result_ibfk_3` (`team_id`),
  KEY `player_id_2` (`player_id`,`role`),
  CONSTRAINT `lcs_result_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `player` (`id`),
  CONSTRAINT `lcs_result_ibfk_3` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1353 DEFAULT CHARSET=utf8 |

Sample of data in table:

+----+-----------+-------+------------+---------------+---------+
| id | player_id | kills | total_gold | riot_match_id | team_id |
+----+-----------+-------+------------+---------------+---------+
|  1 |         1 |     4 |      18403 |          1669 |       1 |
|  2 |         2 |     4 |      16752 |          1669 |       1 |
|  3 |         3 |     3 |      18372 |          1669 |       1 |
|  4 |         4 |     6 |      21601 |          1669 |       1 |
|  5 |         5 |     0 |      13980 |          1669 |       1 |
|  6 |         6 |     1 |      13119 |          1669 |       2 |
|  7 |         7 |     1 |       9006 |          1669 |       2 |
|  8 |         8 |     0 |      10451 |          1669 |       2 |
|  9 |         9 |     4 |      17027 |          1669 |       2 |
| 10 |        10 |     3 |      16244 |          1669 |       2 |
| 11 |         1 |     1 |       6452 |          1670 |       1 |
| 12 |         2 |     0 |       8947 |          1670 |       1 |
| 11 |        13 |     1 |       6099 |          1670 |       1 |
| 12 |        14 |     1 |       8942 |          1670 |       1 |
| 13 |        15 |     0 |       7473 |          1670 |       1 |
+----+-----------+-------+------------+---------------+---------+

Sample of expected result:

+-----------+---------------------+--------------------+
| player_id | total_kills_in_team | total_gold_in_team |
+-----------+---------------------+--------------------+
|         1 |                 20  |            127021  |
|         2 |                 20  |            127021  |
|         3 |                 17  |            89108   |
|         4 |                 17  |            89108   |
|         5 |                 17  |            89108   |
|         6 |                 9   |            65847   |
|         7 |                 9   |            65847   |
|         8 |                 9   |            65847   |
|         9 |                 9   |            65847   |
|        10 |                 9   |            37913   |
|        13 |                 3   |            37913   |
|        14 |                 3   |            37913   |
|        15 |                 3   |            37913   |        
+-----------+---------------------+--------------------+
share|improve this question
    
Does every player belong to one team only? –  ypercube Mar 5 at 21:58
    
Yes, they belong to one team only. Problem is that I want the sum of kills of results that they were part of. 5 players per team but not always the same 5 players in a team. Does that make sense? –  ZiggyZergling Mar 5 at 22:50
    
No. Do you want the sum of kills per team, per match or per team and match? Better to show some samples rows (say 10) and expected results. –  ypercube Mar 5 at 23:31
    
I'm sorry, a bit hard to explain. The goal is to have the sum of all kills (as well as gold as you can see) ever made by the player plus the entire team (so the player itself + 4 team-mates) grouped per player. The reason why I use WHERE IN is because it's not the same group of 5 players for every match. Is that better? –  ZiggyZergling Mar 5 at 23:37
    
Ah, I see now. Is the combination (player_id, team_id, match_id) unique? –  ypercube Mar 5 at 23:48

1 Answer 1

up vote 1 down vote accepted

I guess this is want you want:

For every player, find his team and calculate the sum of kills and golds for all the matches of that team:

SELECT 
    p.player_id,
    SUM(t.kills)      AS total_kills_in_team,
    SUM(t.total_gold) AS total_gold_in_team
FROM 
        ( SELECT player_id, team_id, match_id
          FROM lcs_result
          GROUP BY player_id, team_id, match_id
        ) AS p
    JOIN 
        ( SELECT team_id, match_id
                 SUM(kills) AS kills,
                 SUM(total_gold) AS total_gold
          FROM lcs_result
          GROUP BY team_id, match_id
        ) AS t 
      ON  t.team_id = p.team_id
      AND t.match_id = p.match_id
GROUP BY
    p.player_id ;

If the combination (player_id, team_id, match_id) is unique, then the above is equivalent to:

SELECT 
    p.player_id,
    SUM(t.kills)      AS total_kills_in_team,
    SUM(t.total_gold) AS total_gold_in_team
FROM 
        lcs_result AS p
    JOIN 
        lcs_result AS t 
      ON  t.team_id = p.team_id
      AND t.match_id = p.match_id
GROUP BY
    p.player_id ;
share|improve this answer
    
Wow, you just blew my mind with that. This query completed in 0.01 seconds and mine in 0.22. Any chance you can enlighten me as to why this speed difference? Is join just that much faster than subquery? Also Thank you for your quick reply and answer. I've literally been trying to improve this for days –  ZiggyZergling Mar 6 at 0:02
    
Correlated subqueries like the ones you ahd are not best for performance. With good indexes, they are OK, but it's harder than with simple Joins. You'll probably need some composite indexes anyway, to improve further the efficiency. –  ypercube Mar 6 at 0:05
    
I see. I tried to get the join working but I couldn't figure out how to properly join them. Thank you for showing me that example. I'll add the composite indexes. Thanks again! –  ZiggyZergling Mar 6 at 0:12
    
No time now to investigate properly but an index on (team_id, match_id, kills, total_gold) seems good. Maybe combined with another on (player_id, team_id, match_id) –  ypercube Mar 6 at 0:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.