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]
