I have a site where members rate movies they've seen. I run a routine to recommend 5 titles based on movies they've given a max 5 rating. It's very slow, and I think a better MySQL query can speed it up. I'm running MySQL 3.23.54.
Tables: movies (unique id plus movie info) subcats (movie id, subcategory id) ratings (movie id, user id) Currently: Step 1: // User's top five movie subcategories SELECT COUNT(s.subcategory) AS cnt, s.subcategory FROM ratings r, movies m, movie_subcat s WHERE r.rating = 5 AND r.user_id = $user_id AND r.type = 'movie' AND m.id = r.item AND s.movie = m.id GROUP BY s.subcategory ORDER BY cnt DESC LIMIT 5; // Create a list of subcategory IDs to match Step 2: // Which titles already rated? SELECT item FROM ratings WHERE user_id = $user_id // This is where it slows things down by creating a huge list of ids NOT to match Step 3: SELECT m.id, m.title FROM movies m, movie_subcat s WHERE s.movie = m.id AND m.release < NOW() [AND m.id NOT IN (huge list of ids NOT to match)] [AND s.subcategory IN (list of 5 subcats)] GROUP BY m.id ORDER BY RAND() LIMIT 5 Thanks to any takers! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
