CODEWITHSUNDEEP
mysql
Preston Connors
Preston Connors

Reputation: 407

MySQL: Would INNER JOIN Be The Most Efficient In This Statement?

the following MySQL statement takes a very long time to execute, I think because I'm using gameId IN.

I think the most efficient way would be to use a INNER JOIN, but I'm new to MySQL and I can't come up with the INNER JOIN statement. Can you please help me out? If there is a more efficient way than INNER JOIN, I would like to use that, I'm just not sure if there is.

Statement that needs to be more efficient:

SELECT championId
FROM match_up WHERE
gameId IN
(
    SELECT gameId
    FROM match_up 
    GROUP BY gameId
    HAVING SUM(championId IN (19, 23, 40) AND win = 1) = 3
    AND SUM(championId IN (7, 18) AND win = 0) = 2
)

Thank you in advance for the help and knowledge!

Upvotes: 1

Views: 43

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Here is a method that might perform will with an index on match_up(gameId, win, championId):

select *
from match_up m
where 3 = (select count(*)
           from match_up m2
           where m2.gameId = m.gameId and
                 m2.championId IN (19, 23, 40) and
                 m2.win = 1
          ) and
      2 = (select count(*)
           from match_up m2
           where m2.gameId = m.gameId and
                 m2.championId IN (7, 18) and
                 m2.win = 0
          );

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166346

How about something like

SELECT m.championId
FROM match_up m INNER JOIN
(
    SELECT gameId
    FROM match_up 
    GROUP BY gameId
    HAVING SUM(championId IN (19, 23, 40) AND win = 1) = 3
    AND SUM(championId IN (7, 18) AND win = 0) = 2
) s ON m.gameID = s.gameID

Have a look at the difference between Row Subqueries and Subqueries in the FROM Clause

Upvotes: 1

Related Questions