Mysql advanced SELECT, or multiple SELECTS? Movies keywords

Question

I have a mysql database with movies as follows:

MOVIES(id,title)

KEYWORDS_TABLE(id,key_id) [id is referenced to movies.id, key_id is refernced to keywords.id]

KEYWORDS(id,keyword) //this doesn't matter on my example..

Basically i have movies with their titles and plot keywords for each one, i want to select all movies that have the same keywords with with a given movie id.

I tried something like:

SELECT key_id FROM keywords_table WHERE id=9

doing that in php and storing all the IDs in an array $key_id.. then i build another select that looks like:

SELECT movies.title FROM movies,keywords_table WHERE keywords_table.key_id=$key_id[1] OR keywords_table.key_id=$key_id[2] OR ......... OR keywords_table.key_id=$key_id[n]

This kinda works but it takes too much time as we talk about a database with thousands of thousands of records.

So, any suggestions?? thanks!

Answer 1

One thing you could improve... Instead of writing x = a OR x = b OR x = c you can shorten it to just this: x IN (a, b, c).

SELECT movies.title
FROM movies,keywords_table
WHERE keywords_table.key_id IN ($key_id[1], $key_id[2], ..., $key_id[n])

Note also that you are missing a join condition in your query. You are currently doing a CROSS JOIN, also known as a cartesian product. I think you want this:

SELECT movies.title
FROM movies
JOIN keywords_table
ON movies.id = keywords_table.id
WHERE keywords_table.key_id IN ($key_id[1], $key_id[2], ..., $key_id[n])

This query can return the same movie more than once so you might want to add DISTINCT to remove the duplicates. Also you can do the whole thing in one query instead of two as a further optimization:

SELECT DISTINCT M.title
FROM keywords_table K1
JOIN keywords_table K2
ON K2.key_id = K1.key_id
JOIN movies M
ON K2.id = M.id
WHERE K1.id = 4

Regarding the performance, make sure that you have the primary key set to (id) on movies and to (key_id, id) on keywords_table.

Answer 2

Try using the 'in' keyword instead of building a large number of boolean operations.

SELECT movies.title FROM movies WHERE keyword_table.key_id IN ($key_id[1],..,$key_id[n])

Answer 3

Use a subselect:

SELECT DISTINCT m.title
FROM movies m
WHERE id IN (
    SELECT id
    FROM keywords_table
    WHERE id = 9);