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 |
+-----------+---------------------+--------------------+
Best Answer
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:
If the combination
(player_id, team_id, match_id)is unique, then the above is equivalent to: