MySQL query loads forever

Question

I have a table with 1v1 matches like this:

match_number|winner_id|loser_id
------------+---------+--------
     1      |   1     |   2
     2      |   2     |   3
     3      |   1     |   2
     4      |   1     |   4
     5      |   4     |   1

and I would like to get something like this:

player|matches_won|matches_lost
------+-----------+------------
  1   |     3     |     1
  2   |     1     |     2
  3   |     0     |     1
  4   |     1     |     1

My MySQL Query looks like this

SELECT win_matches."winner_id" player, COUNT(win_matches."winner_id") matches_won, COUNT(lost_matches."loser_id") matches_lost FROM `matches` win_matches
JOIN `matches` lost_matches ON win_matches."winner_id" = lost_matches."winner_id"

I don't know what I did wrong, but the query just loads forever and doesn't return anything

Answer 1

You want to unpivot and then aggregate:

select player_id, sum(is_win), sum(is_loss)
from ((select winner_id as player_id 1 as is_win, 0 as is_loss
       from t
      ) union all
      (select loser_id, 0, 1
       from t
      ) 
     ) wl
group by player_id;

Your query is simply not correct. The two counts will produce the same same value -- COUNT(<expression>) returns the number of non-NULL rows for that expression. Your two counts return the same thing.

The reason it is taking forever is because of the Cartesian product problem. If a player has 10 wins and 10 losses, then your query produces 100 rows -- and this gets worse for players who have played more often. Processing all those additional rows takes time.

If you have a separate players table, then correlated subqueries may be the fastest method:

select p.*,
       (select count(*) from t where t.winner_id = p.player_id) as num_wins,
       (select count(*) from t where t.loser_id = p.player_id) as num_loses
from players p;

However, this requires two indexes for performance on (winner_id) and (loser_id). Note these are separate indexes, not a single compound index.