MySQL Forums
Forum List  »  MyISAM

Re: Fulltext search with order by using filesort
Posted by: Torsten
Date: January 02, 2006 12:20PM

Thank you. This works for me. If I use heap as storage engine mysql does not use the index (with myisam it does) but its fast enough at all.

CREATE TEMPORARY TABLE pxm_tmp_search (PRIMARY KEY(tmp_id), KEY(tmp_tstmp)) ENGINE=HEAP SELECT m_id AS tmp_id, m_tstmp AS tmp_tstmp FROM pxm_board,pxm_thread,pxm_message WHERE b_id=t_boardid AND t_id=m_threadid AND b_active=1 AND MATCH (m_subject,m_body) AGAINST(".$objDb->quote($this->m_sSearchString)." IN BOOLEAN MODE);

SELECT m_id,m_threadid,t_boardid,m_subject,m_userid,m_usernickname,m_userhighlight,m_tstmp FROM pxm_tmp_search tmp,pxm_message m,pxm_thread WHERE m_id=tmp_id AND t_id=m_threadid ORDER BY tmp_tstmp DESC;

Even if i do a simple:

SELECT tmp_id FROM pxm_tmp_search ORDER BY tmp_tstmp DESC;

It does not use the index for 2000 resultrows but the filesort. I don't know why but its fine anyway.

Torsten

Options: ReplyQuote


Subject
Views
Written By
Posted
4728
December 30, 2005 12:37PM
Re: Fulltext search with order by using filesort
2978
January 02, 2006 12:20PM


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.