|
|
Posted by StealthBananaT on 05/25/05 14:52
"Tim Roberts" <timr@probo.com> wrote in message news:b67891ljni0evq14gs8r8tm86ggevq6spp@4ax.com...
> "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?
The code definitely works - I've tried it with fewer records.
There's no index on reviews.filmID. That is a foreign key so that I can follow which film each review is of.
--
StealthBanana
Navigation:
[Reply to this message]
|