I have 3 tables. One is a collections table that gets searched. It contains collections of posts from another table. Fulltext index on the subject. Second one is a lot bigger table that has the posts. Then I have a third table that has each collection id matched with the post id. each collection id could have anywhere from 1-500 posts connected to it. In the posts table there is 2 columns with numbers. I would like to do a fulltext search on the collections table and get the results from it but also get the sum of the 2 number columns. Here is the query I have come up with but it is very slow and very different each time on how long it takes. I will also give the full create tables for the 3 tables. If anyone can help me with this It would be much appreciated. SELECT `collections`.`id`,`collections`.`subject`,`collections`.`numfiles`,`collections`.`groups`,`collections`.`size`,`collections`.`from`,`collections`.`date`,`collections`.`nfo`,sum(mainsubjects.num),sum(mainsubjects.`max`) FROM temp.collections, temp.mainsubjects, collectionpartids WHERE match (`collections`.`subject`) against ('dvdr' in boolean mode) and collections.id = collectionpartids.cid and collectionpartids.fid = mainsubjects.id group by collections.id order by `collections`.`date` desc CREATE TABLE `collections` ( `id` mediumint(7) unsigned NOT NULL auto_increment, `subject` text NOT NULL, `filename` text NOT NULL, `numfiles` smallint(6) unsigned NOT NULL default '0', `groups` text NOT NULL, `partids` mediumtext NOT NULL, `size` bigint(20) unsigned NOT NULL default '0', `from` varchar(90) NOT NULL default '', `date` int(10) unsigned NOT NULL default '0', `group1` mediumint( unsigned NOT NULL, `group2` mediumint( unsigned default NULL, `group3` mediumint( unsigned default NULL, `group4` mediumint( unsigned default NULL, `group5` mediumint( unsigned default NULL, `group6` mediumint( unsigned default NULL, `group7` mediumint( unsigned default NULL, `group8` mediumint( unsigned default NULL, `group9` mediumint( unsigned default NULL, `group10` mediumint( 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 `groupindex` (`group1`,`group2`,`group3`,`group4`,`group5`,`group6`,`group7`,`group8`,`group9`,`group10`), KEY `Size` (`size`), KEY `groups` (`groups`(250)), KEY `nfo` (`nfo`), FULLTEXT KEY `Subject_full` (`subject`) ) ENGINE=MyISAM AUTO_INCREMENT=424366 DEFAULT CHARSET=latin1 MIN_ROWS=100000 MAX_ROWS=200000 AVG_ROW_LENGTH=650 CREATE TABLE `collectionpartids` ( `cid` int(10) unsigned NOT NULL, `fid` int(10) unsigned NOT NULL, PRIMARY KEY (`cid`,`fid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `mainsubjects` ( `id` mediumint( unsigned NOT NULL default '0', `subject` text NOT NULL, `totalbytes` int(10) unsigned NOT NULL, `date` int(10) unsigned NOT NULL default '0', `groups` text NOT NULL, `num` smallint(5) unsigned NOT NULL default '0', `max` smallint(5) unsigned NOT NULL default '0', `from` tinytext NOT NULL, PRIMARY KEY (`id`), KEY `date` (`date`), KEY `numfiles` (`num`,`max`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MIN_ROWS=3000000 MAX_ROWS=10000000 AVG_ROW_LENGTH=195 PACK_KEYS=1

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.

Sign In

[SOLVED] Need help optimizing join

Recommended Posts

bubblegum.anarchy

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information