SEO

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

"Extremest" <admin@binindex> schreef in bericht
news:7I2dnfl829UfVWjbnZ2dnUVZ_ufinZ2d@giganews.com ...
> 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
>

Did you try (this is just a suggestion...) to add an index on `date` and add
in the where clause:
where `date` > someDate

This someDate should be calclulated long enought ago, to get at least 50
records. (i.e 1 month ago, or 6 months ago, depending on the amount of new
records per month in your file)

I think this will trigger MySQL tu use this index, so it will not search the
whole file, but just the found rows, for the file-sort thingie in you
WHERE-clause: [[ match (nfo.nfo) against ('$searchterm' in boolean
ode) ]]

I currently have a key on date. There is a fulltext index on nfo.
It gets searched and then it has to get sorted by date then grab
like last 50 results or so.

"Extremest" <Extremest@extremest.com> schreef in bericht
news:LMqdnZo1Hf1Sl2vbnZ2dnUVZ_ojinZ2d@giganews.com ...
>I currently have a key on date. There is a fulltext index on nfo.
> It gets searched and then it has to get sorted by date then grab
> like last 50 results or so.
>

ok,

and what is the output of:
SELECT year(collections.`date`) as Y1, month(collections.`date`) as M1.
count(collections.`id`)
FROM temp.collections,temp.nfo where match (nfo.nfo) against ('$searchterm'
in boolean mode) and nfo.cid = collections.id group by Y1, M2 where Y1>2004

or, in other words, how big is the chance that you will find 'old' records
that match your search.
If chances are high that you will find about 50 records in the last i.e. 2
month, than the "date>two_month_ago" could speed up your query.

Negative site effect is, of course, that if you do not find 50 records in
this period, less records are returned, and, maybe a new search is needed..