Msg#: 4394475 posted 5:52 pm on Dec 5, 2011 (gmt 0)
I have a MySql table with approximately 2 million records named "ratings" with 3 columns, "user", "item" and "rating". I want to select all users that have rated two particular items but cannot figure out how to do it. I'm thinking it may involve a JOIN, but I'm not sure.
For example, if I wanted to select all "users" that have rated both "item" 344 and "item" 476 and place them into an array, how would I write that?
Msg#: 4394475 posted 7:20 pm on Dec 5, 2011 (gmt 0)
start with people who rated item 344, then inner join people who rated item 476
-- this took me about 6 seconds to run on a 2m row table with no indexes select distinct y1.user_id from your_table y1 inner join your_table y2 on (y2.item=476 and y2.user_id=y1.user_id) where y1.item = 344
-- create this index and you can knock 6 seconds down to a few milliseconds -- execute show indexes from your_table to see if it might already exist create index by_item_and_user_id on your_table(item,user_id);