Fulltext search with order by using filesort
Posted by:
Torsten
Date: December 30, 2005 12:37PM
Hi!
I've a forum with about 500.000 messages. The useres complain about the fulltext search ordering by relevance. They would like to have the results ordered by date (newest first).
Is it possible to tell mysql to use an index for sorting the results?
The table looks like this:
CREATE TABLE `pxm_message` (
`m_id` int(10) unsigned NOT NULL auto_increment,
`m_threadid` int(10) unsigned NOT NULL default '0',
`m_parentid` int(10) unsigned NOT NULL default '0',
`m_userid` int(10) unsigned NOT NULL default '0',
`m_subject` varchar(100) NOT NULL default '',
`m_body` mediumtext NOT NULL,
`m_tstmp` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`m_id`),
KEY `m_tstmp` (`m_tstmp`),
KEY `m_thread` (`m_threadid`,`m_tstmp`),
KEY `m_thread_parent` (`m_threadid`,`m_parentid`),
FULLTEXT KEY `m_search` (`m_subject`,`m_body`)
) TYPE=MyISAM;
If i don't use a sort parameter the search ist very fast (<1 sec), but if i have to sort a large result with mysql using filesort the search takes up to 30 sec. Thats to slow especially when a lot of people do a search at the same time.
Thanks,
Torsten