What does the explain plan look like? It sounds like the query is using the
fulltext index. Remember that only one index per table can be used when
performing a query.
-----Original Message-----
From: Jon Drukman
To: [EMAIL PROTECTED]
Sent: 6/2/04 11:57 AM
Subject: avoiding Using filesort
i've read the ORDER BY optimization page in the manual but i'm stumped
by my inability to get this query to avoid the filesort.
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
