问题描述
MySQL 4.0.23-standard
的人。
我们使用诸如`movies`，`people`等表来存储实际的
电影信息，简短的情节摘要等。这些表格是MyISAM。
有关谁导演或制作电影的信息，我们有表格
，如`movies_directedBy`和`movies_producedBy`，它们有三个
列 - 电影表中的电影ID，
`people`表中的人物ID和备注栏。
这些"关系"表格大多是InnoDB，除了
用于投射信息的表格，`people_castIn`，这是MyISAM，因为这个表格需要全文搜索。
为了决定一个人占主导地位（例如，导演，演员，制作，声音技术等），我们目前使用PHP来
从这些`movies_ *`关系表中获取一行行数
具有给定的人员ID。该表
人ID最多的表被视为代表他或她的主要职业。
我想知道它是否'可以在SQL中完全执行此操作，
因为它真的很慢，特别是当查询涉及10个或更多
的人时，要获取COUNT（*）来自多个表的总和超过300万行的总额
。
有可能吗？
预先感谢您的帮助
Jasper Bryant-Greene
白菜促销
MySQL 4.0.23-standard
I run an online movie database with over 80,000 movies and over 500,000
people stored within.
We use tables like `movies`, `people`, etc. for storing the actual
movie information, a short plot summary, etc. These tables are MyISAM.
For information like who directed or produced a movie, we have tables
like `movies_directedBy` and `movies_producedBy` which have three
columns -- the movie ID in the `movies` table, the person ID in the
`people` table, and a notes column.
These "relationship" tables are mostly InnoDB, with the exception of
the table for casting information, `people_castIn`, which is MyISAM as
fulltext searching is required on this table.
In order to decide what occupation a person is predominantly (e.g.
director, actor, produced, sound tech, etc.) we currently use PHP to
fetch a count of rows from each of these `movies_*` relationship tables
which have a given person ID. The table with the most entries for that
person ID is deemed to represent his or her main occupation.
I''d like to know whether it''s possible to do this entirely in SQL,
since it''s really slow, especially when the query involves 10 or more
people, to fetch COUNT(*)s from multiple tables having a combined total
of over 3 million rows.
Is it possible?
Thanks in advance for any help
Jasper Bryant-Greene
Cabbage Promotions
推荐答案
我建议你从一个更好的规范化方案开始。将您的数据重组为tblMovies，tblDirectors，tblProducers，并且可能需要
投入tblActors以获得良好的衡量标准。确保每个都有主要的
关键字段（自然键或其他）。你的tblMovies表应该
有链接字段到tblDirectors中的主要键字段和
tblProducers以及像[StarActor]& [StarActress]。
但是，我会建议tblMovies
和tblActors之间的联结表，因为电影可以有几个（3 - 5 - 打打...）
联合主演。称之为tblCoStars并为其提供[ActorID]和
[MovieID]字段，它们之间具有1对多的关系
和其他2个表中的每一个， tblCoStars是这两种关系的许多方面。在tblMovies中构建自己的指数
每个字段：[MovieID]（当然），然后[DirectorID]，
[ProducerID]和[ActorID]。
这里有一些示例SQL应该生成很多行
如果你有丰富的&你提到的完整数据集......
SELECT DISTINCTROW tblMovies.MovieName，tblMovies.ProducerID，
tblMovies.DirectorID，tblMovies.ReleaseDate，tblMovies.BoxOfficeGross，
tblMovies.MovieRating，tblMovies.MovieDescrip，[DirectorFName]& " " &
[DirectorLName] AS总监，[ProducerFName]& " " &安培; [ProducerLName]
AS制作人
FROM（tblMovies INNER JOIN tblDirectors ON tblMovies.DirectorID =
tblDirectors.DirectorID）INNER JOIN tblProducers ON
tblMovies.ProducerID = tblProducers.ProducerID
WHERE（（tblMovies.ProducerID = 1或tblMovies.ProducerID = 19或
tblMovies.ProducerID = 39或tblMovies.ProducerID = 101或
tblMovies.ProducerID = 311）AND（tblMovies.DirectorID = 17或
tblMovies.DirectorID = 77或tblMovies.DirectorID = 211或
tblMovies.DirectorID = 321））;
（未经测试）
I suggest you start with a better normalization scheme. Restructure
your data into tblMovies, tblDirectors, tblProducers and maybe
throw in tblActors for good measure. Make sure each has primary
key field (natural keys or otherwise). Your tblMovies table should
have link fields to the primary keyfields in tblDirectors and
tblProducers and maybe fields like [StarActor] & [StarActress].
However, I would suggest a junction table between tblMovies
and tblActors, as movies can have several (3 - 5 - a dozen...)
co-stars. Call it tblCoStars and furnish it with [ActorID] and
[MovieID] fields with a 1-to-many relationship between itself
and each of the other 2 tables, with tblCoStars being the many
side of both relationships. Build yourself indices in tblMovies on
each of these fields: [MovieID] (of course), then [DirectorID],
[ProducerID] and [ActorID].
Here''s some sample SQL that should churn out a lot of rows
if you have the rich & full datasets you mentioned...
SELECT DISTINCTROW tblMovies.MovieName, tblMovies.ProducerID,
tblMovies.DirectorID, tblMovies.ReleaseDate, tblMovies.BoxOfficeGross,
tblMovies.MovieRating, tblMovies.MovieDescrip, [DirectorFName] & " " &
[DirectorLName] AS Director, [ProducerFName] & " " & [ProducerLName]
AS Producer
FROM (tblMovies INNER JOIN tblDirectors ON tblMovies.DirectorID =
tblDirectors.DirectorID) INNER JOIN tblProducers ON
tblMovies.ProducerID = tblProducers.ProducerID
WHERE ((tblMovies.ProducerID=1 Or tblMovies.ProducerID=19 Or
tblMovies.ProducerID=39 Or tblMovies.ProducerID=101 Or
tblMovies.ProducerID=311) AND (tblMovies.DirectorID=17 Or
tblMovies.DirectorID=77 Or tblMovies.DirectorID=211 Or
tblMovies.DirectorID=321));
(untested)
这篇关于我怎么能在SQL中这样做？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！