| 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);
| 8:40 pm on Dec 5, 2011 (gmt 0)|
Thanks, but there's only one table. I think your statement refers to two tables (y1 and y2). Can I simply replace y2 with y1?
Sorry if I'm missing something, but I'm still relatively new to this.
| 8:52 pm on Dec 5, 2011 (gmt 0)|
I got this from someone on another forum, and it works perfectly:
where item in (344,476)
having count(distinct item) = 2
| 8:56 pm on Dec 5, 2011 (gmt 0)|
I like that example more than mine. I learned something, too.
|brotherhood of LAN|
| 9:14 pm on Dec 5, 2011 (gmt 0)|
For reference plogger, you can actually join the same table onto itself, the "AS" allows you to have synonyms so the DB engine can understand which table instance you're referencing.
SELECT * FROM table AS t1 INNER JOIN table AS t2 ON t1.id = t2.id
| 9:29 pm on Dec 5, 2011 (gmt 0)|
That's good to know. Thanks for sharing it!