From: Adam (lists_acccomcast.net)
Date: Thu Feb 05 2004 - 06:36:38 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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/mysql?unsub=lists_acccomcast.net
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]