Mysql advanced SELECT or multiple SELECTS? Keywords in the movie

Question

I have a mysql database with movies:

CINEMA (identifier, name)

KEYWORDS_TABLE (id, key_id) [id is a link to movies.id, key_id - refers to keywords.id]

KEYWORDS (id, keyword) // it doesn’t matter in my example ..

Basically, I have films with their names and subject keywords for each of them, I want to select all films with the same keywords with the specified movie identifier.

I tried something like:

SELECT key_id FROM keywords_table WHERE id = 9

doing this in php and storing all the Identifiers in the $ key_id array .. then I build another choice that looks like this:

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 view works, but it takes too much time when we are talking 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 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]) 

Please note that there is no join condition in your request. You are currently making CROSS JOIN, also known as a decartive 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 can add DISTINCT to remove duplicates. You can also do everything in one request instead of two as an additional 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 

As far as performance is concerned, make sure that you have set the primary key (id) to movies and (key_id, id) to keywords_table .

Answer 2

Try using the 'in' keyword instead of creating a large number of logical operations.

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

Answer 3

Use the subquery:

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