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

[Jasper Bryant-Greene]

Thanks everyone for your help. Kevin -- once we have the process of
actually figuring out the occupation sorted, yes, we will cache the
data in a simple `occupation` field in the `people` table. We need to
reliably calculate this information first though.

What I have now is the following SQL query:

SELECT b.name, COUNT(a.movie) AS n, c.id AS movieID, c.title, c.year
FROM movies_people AS a, occupations AS b, movies AS c
WHERE a.person=$id AND b.id=a.occupati on AND c.id=a.movie
GROUP BY a.occupation
ORDER BY n DESC
LIMIT 1

the `movies_people` table is a link between the movie and the person,
the `occupations` table is a table of possible occupations, and the
`movies` table is the master movies table.

This returns me a single row containing the name of the person's
predominant occupation, for example director, and details about a movie
which they "performed that occupation" on, for example a movie they
directed.

The problem is, I want to retrieve the newest movie they performed this
occupation on. In other words, I want the row from the `movies` table
with the highest value for `year`. I've tried fiddling with GROUP BY
and ORDER BY clauses to no avail. Any clues?

By the way -- very good point regarding taking time into account. I
will look at this once I have sorted these issues.

Thanks again everyone for your help. Best regards

[Bill Karwin]

Jasper Bryant-Greene wrote:

SELECT b.name, COUNT(a.movie) AS n, c.id AS movieID, c.title, c.year
FROM movies_people AS a, occupations AS b, movies AS c
WHERE a.person=$id AND b.id=a.occupati on AND c.id=a.movie
GROUP BY a.occupation
ORDER BY n DESC
LIMIT 1

The problem is, I want to retrieve the newest movie they performed this
occupation on. In other words, I want the row from the `movies` table
with the highest value for `year`.

I think in this case you're trying to get too many answers from one
query. As you've seen, you're getting values from an arbitrary record
in the movies table, out of those that match the grouping by a.occupation.

IMHO, MySQL fails to obey proper SQL standards, by allowing you to ask
for c.* in this query. This should be an error in SQL, because it gives
ambiguous results, because you aren't grouping by a unique key of the
movies table. In MySQL's case, it generally gives you the results from
the first row that matches based on the physical ordering of rows in the
database file; this ordering is arbitrary and in general beyond your
control.

But if you were to use GROUP BY c.id, of course this would not give the
desired results for COUNT(a.movie), which you want to be counted per
occupation type.

This is a pretty good clue that you're actually doing two different
queries, but trying to force them into one SQL statement.
It's not worth it, man! Don't be a hero! <g>

Regards,
Bill K.