Mysql高级SELECT，还是多个SELECTS？电影关键字 [英] Mysql advanced SELECT, or multiple SELECTS? Movies keywords

问题描述

我有一个包含电影的mysql数据库，如下所示：

I have a mysql database with movies as follows:

MOVIES（id，title）

MOVIES(id,title)

KEYWORDS_TABLE（id，key_id）[id是
引用movies.id，key_id是
引用至keywords.id]

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

KEYWORDS（id，keyword）//对我的例子无所谓。

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

基本上我有电影

我尝试过类似的操作：

SELECT key_id FROM keywords_table
WHERE id = 9

SELECT key_id FROM keywords_table WHERE id=9

在php和储存数组中的所有
ID $ key_id ..然后我构建
另一个选择，如下所示：

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

选择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]

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!

推荐答案

有一件事你可以改进...而不是写 x = a OR x = b OR x = c 您可以将其缩短为 x IN（a，b，c）。

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])

您的查询。你现在正在做CROSS JOIN，也被称为笛卡尔产品。我想你想要这个：

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])

此查询可以多次返回同一个电影，因此您可能需要添加 DISTINCT 删除重复项。另外，你可以在一个查询中做整个事情，而不是两个作为进一步优化：

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

关于性能，请确保您的主键设置为 和（key_id，id）上的< $ c> keywords_table 。

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

这篇关于Mysql高级SELECT，还是多个SELECTS？电影关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！