Calling all QUERY-GURUS! Help on Collaborative Filtering

[Jennifer Moss]

Any experience with collab filtering out there? (ie. "People who liked THISBOOK also liked BOOKA, BOOKB, BOOKC")

I am trying to do a simple collaborative filtering query on one table, and it's crashing the db--so I must be doing sumpin' wrong.

The table has over 4 million records in it and is indexed on both BOOKTITLE and CUST_ID.

My query:
============
SELECT DISTINCT BOOKTITLE, Count(CUST_ID) CT
From BOOKS
WHERE (BOOKTITLE!='DaVinci Code')
AND CUST_ID IN (SELECT CUST_ID from BOOKS WHERE BOOKTITLE='DaVinci Code')
GROUP BY BOOKTITLE
ORDER By CT DESC
LIMIT 10
============

One person suggested making a second identical table and doing the subselect on that, since it wouldn't be recursive.

Any ideas??



Edited 1 time(s). Last edit at 01/11/2007 11:13AM by Jennifer Moss.