Optimize Query

[Psycho]

I have a query that is taking several seconds to process and was wondering if anyone had any ideas on how to get the same data with a more efficient query.

I have two tables that are involved. There are additional fields, but I am only showing the ones relevant to the query I need.
[movies_crew]
- movie_id
- crew_id (unique to a record in the crew table)
- role_id

[roles]
- role_id
- role

What I am trying to obtain is the number of unique crewmembers for each role. For example "Tom Hanks" might be an actor in several movies, but I only want him counted once when determining the number of actors. However, "Tom Hanks" could also be a Producer and would need to be counted in that role calculation.

The result set I am looking for is like this:
Actor, 3,567
Director, 356
Producer, 512
Writer, 435

I am already getting those results with the query below, but it takes several seconds to run.
[code]SELECT r.role as role, COUNT(DISTINCT mc.crew_id) as count
FROM movies_crew mc
LEFT JOIN roles r ON mc.role_id = r.role_id
GROUP BY r.role_id
ORDER BY role ASC[/code]

[designationlocutus]

Hmm I've often done counters. To save time on query execution, I created an additional table that stores the count totals. On insert or update of your Actor, Director, Producer, Writer tables, an additional query is executed that counts these and stores the totals.

Very useful when you have thousands upon thousands of records that are always being counted on.

[printf]

Could you please show your real table scheme(s) and tell me what version (#.#) of database are you using.

printf

[Psycho]

MySQL version is 5.0.24a

Here are the tables involved (I have left off some fields that are extraneous):

[movies]
- movie_id (KEY)

[crew]
- crew_id (KEY)

[roles]
- role_id (Key)

[movies_crew]
- index (KEY)
- movie_id (F Key)
- crew_id (F Key)
- role_id (F Key)

As you can see I have three tables that are linked with a fourth. I am trying to find out how many unique crew_id's are associated with each role_id.

I have been able to get the results much faster by using a sub query, but I would appreciate if anyone has any comments on what I am doing and if this is a good approach. The new query I am using is as follows:
[code]SELECT subq.role as role, COUNT(subq.crew_id) as count
FROM
    (SELECT DISTINCT r.role as role, mc.crew_id as crew_id
    FROM roles r
    INNER JOIN movies_crews mc
        ON r.role_id = mc.role_id) as subq
GROUP BY subq.role
ORDER BY subq.role[/code]

[fenway]

I'd need to see the explain output from both queries.

[Psycho]

Thank you for your time.

[b]Query #1[/b]
This was my original query which could take a few seconds to run
[code]SELECT r.role as role, COUNT(DISTINCT mc.crew_id) as count
FROM roles r
INNER JOIN movies_crews mc ON r.role_id = mc.role_id
GROUP BY r.role_id
ORDER BY role ASC[/code]

This is the EXPLAIN output:
[code]id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1  | SIMPLE      | r     | ALL  |               |     |         |     | 24   | Using temporary; Using filesort
1  | SIMPLE      | mc    | ALL  |               |     |         |     | 8101 | Using where[/code]

[b]Query #2[/b]
This is my new query which is running much faster. I'm just not sure this is the most efficient.
[code]SELECT subq.role as role, COUNT(subq.crew_id) as count
FROM (SELECT DISTINCT r.role as role, mc.crew_id as crew_id
      FROM roles r
      INNER JOIN movies_crews mc
        ON r.role_id = mc.role_id
     ) as subq
GROUP BY subq.role
ORDER BY subq.role[/code]

Here is the EXPLAIN output
[code]id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1  | PRIMARY     |       | ALL  |               |     |         |     | 5214 | Using temporary; Using filesort
2  | DERIVED     | r     | ALL  |               |     |         |     | 24   | Using temporary
2  | DERIVED     | mc    | ALL  |               |     |         |     | 8101 | Using where [/code]

[fenway]

Why am I seeing nothing from keys... even if you have none, it should be saying "null"....