MySQL Forums
Forum List  »  MyISAM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Fulltext search with order by using filesort
4728
December 30, 2005 12:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.