Posted by Tim Roberts on 05/25/05 09:37

"StealthBananaT" <noone@nowhere.com> wrote:

>My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the
>count of its reviews, MySQL locks and I have to restart the service.
>
>Here's the SQL...
>
>SELECT films.*, COUNT(reviews.id)
> FROM films LEFT JOIN reviews ON films.id = reviews.filmID
> GROUP BY films.id
> LIMIT 0, 100;
>
>JOIN works but doesn't return records for films with no reviews. Please help!

This shouldn't work at all unless "id" is the only field in "films". The
GROUP BY clause must include every field in the SELECT list that isn't in
an aggregate function.

Do you have indexes on films.id, reviews.id, and reviews.filmID?
--
- Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

[Back to original message]