Terrible title, sorry for not being able to concisely articulate this question.
- I have a MySQL table (Table name: users) with 2m+ users (rows) in it. Each one has a score for that user. (Columns: userid, name, score)
- I need to apply ‘interests’ to each user, so I have created an ‘interests’ table with columns (Columns: userid, interest). A new row is created each time an interest is assigned to a user.
- I then need to select 50 users where their interest = ‘surfing’ and their score is between 10,000 and 50,000
There might be 500,000 users with a score in that range.
SELECT a.userid, a.interest, b.name, b.score FROM interests AS a LEFT JOIN (SELECT userid, name, score FROM users WHERE score > 10000 AND score < 50000) AS b ON a.userid = b.userid WHERE a.interest = 'surfing' ORDER BY b.score DESC LIMIT 50
So I think my above query will work, but I’m not sure I’m going about it in an efficient way. My understanding is that it’s essentially selecting all interests rows where the interest = ‘surfing’ (this might be 50,000 rows) then performing a JOIN on the user table which itself might return 500,000 rows.
select i.userid, i.interest, u.name, u.score from interests i inner join users u on i.userid = u.userid where u.score between 1000 and 50000 and i.interest = 'surfing' order by u.score desc limit 50
Remember to add the following indexes:
- INTERESTS: userid, interest
- USERS: userid, score
Answered By – javier_domenech
Answer Checked By – Dawn Plyler (BugsFixing Volunteer)