Posted by StealthBananaT on 05/25/05 14:53

"Michael Vilain" <vilain@spamcop.net> wrote in message news:vilain-2E9C3E.20060724052005@comcast.dca.giganews.com...
> In article <d70ip2$ouj$1@news8.svr.pol.co.uk>,
> "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!
>
> Are films.id and reviews.filmID indexes?
>

Both tables have id as their index. filmID is a foreign key so that I know which film the review is of. Here's the SQL that
created both tables...

CREATE TABLE `films` (
`id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
)

CREATE TABLE `reviews` (
`id` int(10) unsigned NOT NULL auto_increment,
`filmID` int(10) unsigned NOT NULL default '0',
`author` varchar(20) NOT NULL default '',
`date` int(10) unsigned NOT NULL default '0',
`region` tinyint(1) unsigned NOT NULL default '0',
`review` longtext NOT NULL,
`rating` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
)

--

StealthBanana

[Back to original message]