the table structure is:
CREATE TABLE story ( id int(11) NOT NULL auto_increment, headline text, deck text, free_override tinyint(1) NOT NULL default '0', output_path varchar(255) NOT NULL default '', rank smallint(5) unsigned default '0', has_downloads tinyint(1) NOT NULL default '0', has_movies tinyint(1) NOT NULL default '0', has_screens tinyint(1) NOT NULL default '0', pages mediumint(8) unsigned NOT NULL default '0', ext_url varchar(255) default '', post_date datetime default '0000-00-00 00:00:00', mod_date datetime default '0000-00-00 00:00:00', mod_by varchar(32) default '', status tinyint(4) NOT NULL default '0', type tinyint(4) NOT NULL default '0', subtype tinyint(4) NOT NULL default '0', mag_id varchar(4) default '', author varchar(64) default '', essential_dlx tinyint(4) default '0', essential tinyint(4) default '0', graphic varchar(255) default '', user_level tinyint(4) NOT NULL default '0', top_slot tinyint(4) default '0', stylesheet text, news_industry tinyint(1) NOT NULL default '0', editor_id int(11) default '0', PRIMARY KEY (id), KEY status (status), KEY editors (editor_id), KEY ext_url (ext_url), KEY st (subtype), KEY post_date (post_date), KEY type (type), FULLTEXT KEY jsd (headline,deck) ) TYPE=MyISAM;
the query is:
select id, headline, date_format(s.post_date,'%Y/%m/%d') directory, post_date sort_date
from story s
where type=3 and status=9
and post_date > date_sub(now(),interval 80 day)
and match(headline,deck) against ('halo')
order by post_date desc limit 10
the match() forces filesort to show up in the explain. if i remove it, i can get rid of the filesort and go back up to 80 days with the post_date key.
is a fulltext match just doomed to always use filesort? seems odd to me. i'd think the date restriction would limit the # of rows to be searched more effectively.
-jsd-
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
