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

"ZeldorBlat" <zeldorblat@gmail.com> wrote in message news:1117039743.194278.262400@g44g2000cwa.googlegroups.com...
> But you're joining the reviews table on reviews.filmID -- all the more
> reason to create an index on it. With no index, the database is likely
> scanning the reviews table for every row in films...in other words,
> it's scanning through 20,000 records 10,000 times over.
>

I've added an index to filmID (I think), but I'm having the same problem.

According to MySQLCC, the SQL that would create the reviews table now looks like this

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`),
KEY `FOREIGN` (`filmID`)
);

Any help would be greatly appreciated.

--

StealthBanana

• Next in forum: Re: LEFT JOIN on very large tables
• Prev in forum: Re: Help with trick MySQL select problem
• Thread view: Re: LEFT JOIN on very large tables

[Reply to this message]