How to automatically exclude items already visited in recommendation algorithm?

Question

I'm now using slope One for recommendation.

How to exclude visited items from result?

I can't do it simply by not in (visited_id_list) to filter those visited ones because it will have scalability issue for an old user!

I've come up with a solution without not in：

select b.property,count(b.id) total from propertyviews a
                                         left join propertyviews b on b.cookie=a.cookie
                                         left join propertyviews c on c.cookie=0 and b.property=c.property
                                         where a.property=1 and a.cookie!=0 and c.property is null
                                         group by b.property order by total;

Answer 1

Seriously, if you are using MySQL, look at 12.2.10.3. Subqueries with ANY, IN, and SOME

For example:

SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

This is available in all versions of MySQL I looked at, albeit that the section numbers in the manual are different in the older versions.

EDIT in response to the OP's comment:

1. OK ... how about something like SELECT id FROM t1 WHERE ... AND NOT id IN (SELECT seen_id FROM user_seen_ids where user = ? ). This form avoids having to pass thousands of ids in the SQL statement.

2. If you want to entirely avoid the "test against a list of ids" part of the query, I don't see how it is even possible in theory, let alone how you would implement it.