Posted by Michael Vilain on 05/26/05 10:24

In article <d73erj$65s$1@newsg3.svr.pol.co.uk>,
"StealthBananaT" <noone@nowhere.com> wrote:

> "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.

what happens if you try using the "EXPLAIN" directive to have MySQL tell
you what it would try to do for your query?

--
DeeDee, don't press that button! DeeDee! NO! Dee...

[Back to original message]