CODEWITHSUNDEEP
mysqljoin
user34790
user34790

Reputation: 2036

Handling join operation efficiently

I have these two tables.

Table 1 with 10 million entries having columns gameid and gamename Table 2 with few thousand entries having columns userid and gamename. I want to get the corresponding gameid for each user by mapping the gamenames from the two tables.

My query is like this 

SELECT game.id FROM game RIGHT JOIN player ON game.game_name=player.game_name

But it's like taking ages. What could be the efficient way to do it?

Upvotes: 2

Views: 37

Answers (2)

Andy Jones
Andy Jones

Reputation: 6275

Consider using...

EXPLAIN SELECT game.id FROM game RIGHT JOIN player ON game.game_name=player.game_name

The initial EXPLAIN will get MySQL to tell you what it's doing with a good explanation of the output being here.

You most likely want some sort of index on both game.game_name and player.game_name.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269933

If you want the lookup to go quickly, then create an index on game(game_name, gameid).

This should be reasonable, assuming that there are few duplicate names in the game table. If there are, your problem may also be sheer volume of data being returned.

Upvotes: 2

Related Questions