Sorry for the obscurity. Although I'm not new to MySQL, I'm new to the list.
MOVIES --------------------------- | id | title | year | --------------------------- | 1083 | Star Wars | 1977 | --------------------------- SUBCATS ----------------------- | movie | subcategory | ----------------------- | 1083 | 181 | ----------------------- | 1083 | 179 | ----------------------- | 1083 | 184 | ----------------------- | 1083 | 3 | ----------------------- | 1083 | 91 | ----------------------- RATINGS ----------------------------------- | item | type | user_id | rating | ----------------------------------- | 1083 | movie | 821 | 5 | ----------------------------------- Using the code in the original message below, let's assume this user rated Star Wars a 5. I want to find other movies he has NOT rated yet with the same subcategories as Star Wars. - Mark -----Original Message----- From: Adam [mailto:[EMAIL PROTECTED] Sent: Thursday, February 05, 2004 7:37 AM To: Mark Cc: MySQL List Subject: Re: Faster version of Movie Search Mark, You're killing me because I can't visualize the source tables. A query like this is bread and butter, it shouldn't take to long to execute. Give me a better idea of the table structure and relationships between them. I try to help. Regards, Adam On Feb 4, 2004, at 11:04 PM, Mark wrote: > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
