How can I do this in SQL?

[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_directe dBy` and `movies_produce dBy` which have three
columns -- the movie ID in the `movies` table, the person ID in the
`people` table, and a notes column.

These "relationsh ip" 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

[MLH]

<snip>

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_directe dBy` and `movies_produce dBy` which have three
columns -- the movie ID in the `movies` table, the person ID in the
`people` table, and a notes column.

These "relationsh ip" 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.

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.Movie Name, tblMovies.Produ cerID,
tblMovies.Direc torID, tblMovies.Relea seDate, tblMovies.BoxOf ficeGross,
tblMovies.Movie Rating, tblMovies.Movie Descrip, [DirectorFName] & " " &
[DirectorLName] AS Director, [ProducerFName] & " " & [ProducerLName]
AS Producer
FROM (tblMovies INNER JOIN tblDirectors ON tblMovies.Direc torID =
tblDirectors.Di rectorID) INNER JOIN tblProducers ON
tblMovies.Produ cerID = tblProducers.Pr oducerID
WHERE ((tblMovies.Pro ducerID=1 Or tblMovies.Produ cerID=19 Or
tblMovies.Produ cerID=39 Or tblMovies.Produ cerID=101 Or
tblMovies.Produ cerID=311) AND (tblMovies.Dire ctorID=17 Or
tblMovies.Direc torID=77 Or tblMovies.Direc torID=211 Or
tblMovies.Direc torID=321));

(untested)

[MLH]

<big snip>
If you call your junction table tblCast & give it 2 fields: [MovieID]
and [ActorID], you could put it in the query to include co-stars in
the query dynaset.

The following should produce list of movies, producers, directors,
actors for a number of films directed by any of several different
directors OR produced by any of several different producers.

SELECT DISTINCTROW tblMovies.Movie Name, tblMovies.Produ cerID,
tblMovies.Direc torID, tblMovies.Relea seDate, tblMovies.BoxOf ficeGross,
tblMovies.Movie Rating, tblMovies.Movie Descrip, [DirectorFName] & " " &
[DirectorLName] AS Director, [ProducerFName] & " " & [ProducerLName]
AS Producer, [ActorFName] & " " & [ActorLName] AS Actor
FROM tblActors INNER JOIN (((tblMovies INNER JOIN tblDirectors ON
tblMovies.Direc torID = tblDirectors.Di rectorID) INNER JOIN
tblProducers ON tblMovies.Produ cerID = tblProducers.Pr oducerID) INNER
JOIN tblCast ON tblMovies.Movie ID = tblCast.MovieID ) ON
(tblMovies.Acto rID = tblActors.Actor ID) AND (tblActors.Acto rID =
tblCast.ActorID )
WHERE ((tblMovies.Pro ducerID=1 Or tblMovies.Produ cerID=19 Or
tblMovies.Produ cerID=39 Or tblMovies.Produ cerID=101 Or
tblMovies.Produ cerID=311)) OR ((tblMovies.Dir ectorID=17 Or
tblMovies.Direc torID=77 Or tblMovies.Direc torID=211 Or
tblMovies.Direc torID=321));

[Jasper Bryant-Greene]

Hi

Thanks for your reply, but I think you have misunderstood me. I'm not
looking to create lists of actors, producers, directors, etc. for
movies. We already do that and it works fine. What I am looking to do
is create an SQL query that will do the equivilant of what I am
currently doing in PHP, which is:

1. Given a person ID, get the number of records in about 10-15
different tables (directors, producers, actors, crewmembers, sound
techs, art department, makeup, sfx, etc.).
2. Return the name of the table which had the largest number of
records.

This tells me what the person's predominant occupation is. The problem
is, step 1 requires 10-15 different SQL queries executing COUNT(*)
operations. I'm looking for a way to do it all in a single, or at least
in fewer, SQL queries, to improve performance.

Also the scheme you have suggested requires a huge amount of data
redundancy as a single person could be an actor, a producer, a
director, a crewmember and much more. In your scheme there would be
information stored seperately for each occupation of each person, an
exponentially larger amount of information. We can't afford for that
much data redundancy when we're dealing with millions of records.

We already have many more tables (actors, crewmembers, art dep., makeup
dep., sfx, visual fx, sound techs, motion control, etc. etc.) but I
didn't list them all as it wasnt necessary.

Jasper

[Felix Geerinckx]

On 07/04/2005, Jasper Bryant-Greene wrote:

We already have many more tables (actors, crewmembers, art dep.,
makeup dep., sfx, visual fx, sound techs, motion control, etc. etc.)
but I didn't list them all as it wasnt necessary.

Why do you make different tables for different occupations instead of
just a people-table, a movie-table and a table linking people/movies
with the occupation as extra attribute?

Then you can simply say:

SELECT
peoplemovies.oc cupation,
COUNT(peoplemov ies.id) AS number
FROM peoplemovies
WHERE
peoplemovies.pe ople_id = <<whatever>>
GROUP BY peoplemovies.oc cupation
ORDER BY number DESC
LIMIT 1

--
felix

[Archie Ball]

<snip>
I apologize. I went back and read your question.
Here it is...

Is it possible?

Answer: Yes.

However, I would never consider constructing
a set of database tables as large as those you
described in the same manner you did. Follow
my other instructions and your sql will be much
more efficient and easier to write.

[Jasper Bryant-Greene]

Felix -- we don't create seperate tables.

There is one `people` table which contains all the information specific
to that person.

There is then a huge set of linking tables, `movies_directe dBy`,
`movies_written By`, `movies_makeupB y`, `movies_soundBy `, `movies_artBy`
which contain just three columns -- the ID of the movie, the ID of the
person, and a notes column.

This is the best way to do it -- the method that Archie suggests is
not. If I followed Archie's instructions I would have the same number
of tables (one for each occupation) but I would have a lot of data
redundancy which is not acceptable.

I apologise if I haven't made myself clear in previous posts.

[Jasper Bryant-Greene]

Sorry Felix, I re-read your post and now see what you are suggesting.
Excellent idea, I'm going to implement that.

Thanks

[Bill Karwin]

Jasper Bryant-Greene wrote:

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 think the following might work, but I haven't tried it so I'm not sure
if there's a logical conflict with doing multiple aggregate functions in
this case.

SELECT p.person_ID,
COUNT(d.movie_I D) AS directedByCount ,
COUNT(w.movie_I D) AS writtenByCount
FROM people AS p
LEFT OUTER JOIN movies_directed By AS d ON p.persion_ID = d.person_ID
LEFT OUTER JOIN movies_writtenB y AS w ON p.persion_ID = w.person_ID
WHERE p.person_ID IN ( $my_person_id_l ist )
GROUP BY p.person_ID

It should be clear how to add extra joins and COUNT()'s for your other
matching tables. But keep in mind that MySQL has a limit of 31 tables
in a single join (or 63 tables if you're running on a 64-bit operating
system).

Then you can do your comparison between the counts in your PHP code and
decide who is a director, who is a writer, etc.

It occurs to me, though, do you make any allowances for _when_ the
person was a director vs. a writer? What if they were a writer from
1970-1979 and they wrote a lot of movies, but they've been a director
ever since 1979, though for for a smaller total number of projects? :-)

Regards,
Bill K.

[Kevin Sproule]

"Jasper Bryant-Greene" <ja******@gmail .com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

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_directe dBy` and `movies_produce dBy` which have three
columns -- the movie ID in the `movies` table, the person ID in the
`people` table, and a notes column.

These "relationsh ip" 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

Jasper,

Why are you doing dynamic queries on a relatively static database? Sure you
are adding new data but the new data is a very small percentage of the
total. Why not run a batch process on a regular basis to determine the
predominate occupation of a person? You could also take into account time
frames to give a more accurate occupation or just list the number of times
the person has acted, directed, produced, etc.. Storing this data and
updating it when needed will save you a vast amount of resources.

Kevin Sproule