I need help optimizing this join

[binindex]

Here is the query I am currently using.

It seems to take forever. It keeps wanting to use filesort and a temporary can anyone plz help me. I will send the creates for both tables.

 

select collections.`id`,collections.`subject`,collections.`numfiles`,collections.`groups`,

collections.`size`,collections.`from`,collections.`date`,collections.`nfo`

FROM temp.collections,temp.nfo where match (nfo.nfo) against ('$searchterm' in boolean mode) and nfo.cid = collections.id order by `date` desc limit 0,50

 

CREATE TABLE `nfo` (

`id` mediumint(6) unsigned NOT NULL auto_increment,

`cid` int(10) unsigned NOT NULL,

`fid` int(10) unsigned NOT NULL,

`nfo` mediumtext NOT NULL,

`filename` varchar(200) NOT NULL,

PRIMARY KEY (`id`),

KEY `cid` (`cid`),

KEY `fid` (`fid`),

FULLTEXT KEY `nfo` (`nfo`)

) ENGINE=MyISAM AUTO_INCREMENT=152507 DEFAULT CHARSET=latin1

 

CREATE TABLE `collections` (

`id` int(10) unsigned NOT NULL auto_increment,

`subject` text NOT NULL,

`filename` text NOT NULL,

`numfiles` smallint(4) unsigned NOT NULL default '0',

`groups` text NOT NULL,

`partids` mediumtext NOT NULL,

`size` bigint(20) unsigned NOT NULL default '0',

`from` tinytext NOT NULL,

`date` int(10) unsigned NOT NULL default '0',

`group1` smallint(4) unsigned NOT NULL,

`group2` smallint(4) unsigned default NULL,

`group3` smallint(4) unsigned default NULL,

`group4` smallint(4) unsigned default NULL,

`group5` smallint(4) unsigned default NULL,

`nfo` int(10) unsigned NOT NULL default '0',

PRIMARY KEY (`id`),

UNIQUE KEY `filename_U` (`filename`(255)),

KEY `from` (`from`(50)),

KEY `date` (`date`),

KEY `Size` (`size`),

KEY `nfo` (`nfo`),

KEY `groupindex` USING BTREE (`group1`,`group2`,`group3`,`group4`,`group5`),

FULLTEXT KEY `Subject_full` (`subject`)

) ENGINE=MyISAM AUTO_INCREMENT=1865103 DEFAULT CHARSET=latin1 MIN_ROWS=100000 MAX_ROWS=2500000 AVG_ROW_LENGTH=500

[effigy]

Post an EXPLAIN SELECT...

[binindex]

1, 'SIMPLE', 'nfo', 'fulltext', 'cid,nfo', 'nfo', '0', '', 1, 'Using where; Using temporary; Using filesort'

1, 'SIMPLE', 'collections', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'temp.nfo.cid', 1, ''

[fenway]

It can't use both indexes....