Posted by StealthBananaT on 05/26/05 06:13

"Michael Vilain" <vilain@spamcop.net> wrote in message news:vilain-505DEC.09413125052005@comcast.dca.giganews.com...
> In article <d71ov0$sc9$1@newsg3.svr.pol.co.uk>,
> "StealthBananaT" <noone@nowhere.com> wrote:
>
>> "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`)
>> )
>
> In order to not do a linear table scan, you must create indices on all
> fields that require random access or are part of a join. Create an
> index for the reviews.filmID field.
>

I'd like to thank everybody for their help, but the problem remains. I've tried making reviews.filmID an index but that seemed to
have no effect.

I have a third table called users, and when I try to count the number of reviews submitted by users I have exactly the same
problem...

SELECT users.name, COUNT(reviews.id)
FROM users LEFT JOIN reviews ON users.name = reviews.author
GROUP BY users.name;

This is the users table...

CREATE TABLE `users` (
`name` varchar(20) NOT NULL default '',
`password` varchar(40) NOT NULL default '',
`level` tinyint(1) unsigned NOT NULL default '0',
`email` varchar(100) NOT NULL default '',
`sessionID` varchar(100) default '0',
PRIMARY KEY (`name`)
);

I'm guessing that the problem is linked to the sheer volume of the data in the tables.

--

StealthBanana

[Back to original message]