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

Question

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!

Adriaan Stander · Accepted Answer · 2014-06-09 19:24:27Z

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

Gordon Linoff · Accepted Answer · 2014-06-09 19:28:46Z

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 );

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related